package com.hh.bss.report.dao.impl;

import static com.hh.bss.util.ObjectUtils.isNotEmpty;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.type.descriptor.java.DataHelper;

import com.hh.bss.auth.entity.SystemUser;
import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.BaseHibernateDao;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.cache.SystemUserCache;
import com.hh.bss.common.page.Page;
import com.hh.bss.project.entity.ContractJobCvJd;
import com.hh.bss.report.dao.AccountReportDao;
import com.hh.bss.report.vo.query.ReportQuery;
import com.hh.bss.util.DateHelper;
import com.hh.bss.util.FileHelper;
import com.hh.bss.util.StringHelper;

public class AccountReportDaoImpl  extends BaseHibernateDao implements AccountReportDao {

	//员工业绩报表
	public List accountYJSum(ReportQuery query) {
		// TODO Auto-generated method stub
		 StringBuffer sql2 = new StringBuffer();		 
	        sql2.append("select * from ( ");
	        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
	        sql2.append(" select a.id,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,j.conf1 q1t,round(ifnull(b.q1,0),2) q1,if(j.conf1=null,null,concat(round(round(ifnull(b.q1,0),2)/j.conf1*100,2),'%')) q1r,j.conf2 q2t,round(ifnull(b.q2,0),2) q2,if(j.conf2=null,null,concat(round(round(ifnull(b.q2,0),2)/j.conf2*100,2),'%')) q2r,j.conf3 q3t,round(ifnull(b.q3,0),2) q3,if(j.conf3=null,null,concat(round(round(ifnull(b.q3,0),2)/j.conf3*100,2),'%')) q3r,j.conf4 q4t,round(ifnull(b.q4,0),2) q4,if(j.conf4=null,null,concat(round(round(ifnull(b.q4,0),2)/j.conf4*100,2),'%')) q4r,j.conf5 qat,round(ifnull(b.qa,0),2) qa,if(j.conf5=null,null,concat(round(round(ifnull(b.qa,0),2)/j.conf5*100,2),'%')) qar  from system_user a ");
	        //Q1
	        //account_ar
	        String curYear=query.getYear();
	        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
	        int inextYear=StringHelper.parseInt(curYear, 0)+1;
	        String nextYear=String.valueOf(inextYear);
	         
	        sql2.append(" left join ( ");
	        sql2.append(" select e.account, ");
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-01-01',if(f.send_dt<'").append(curYear).append("-04-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) q1, ");
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-04-01',if(f.send_dt<'").append(curYear).append("-07-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) q2, ");
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-07-01',if(f.send_dt<'").append(curYear).append("-10-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) q3, ");
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-10-01',if(f.send_dt<'").append(nextYear).append("-01-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) q4, ");
	        
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-01-01',if(f.send_dt<'").append(curYear).append("-04-01',ifnull(f.final_ar,0)*e.percent/100,0),0))  ");
	        sql2.append("+sum(if(f.send_dt>='").append(curYear).append("-04-01',if(f.send_dt<'").append(curYear).append("-07-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) ");
	        sql2.append("+sum(if(f.send_dt>='").append(curYear).append("-07-01',if(f.send_dt<'").append(curYear).append("-10-01',ifnull(f.final_ar,0)*e.percent/100,0),0))  ");
	        sql2.append("+sum(if(f.send_dt>='").append(curYear).append("-10-01',if(f.send_dt<'").append(nextYear).append("-01-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) qa ");
	        	        
	        
	        //sql2.append("  from  account_ar a ");
	        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
	        //sql2.append("  from  account_pn b ");
	        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
	        sql2.append("  from  contract_job_cv_offer c ");
	        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
	        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0))/100 rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
	        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
	        sql2.append("   inner join account_pn f on c.id=f.offer_id  ");
	        sql2.append(" group by  e.account ");
	        sql2.append(" ) b on b.account=a.account  ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");	        
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
	        //业绩指标
	        sql2.append(" left join  system_user_report_conf j  on a.account=j.account and j.type=3700 ");
	        sql2.append(" ) t where 1=1 ");
	        List values = new ArrayList();
	       
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	        
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        }
	        
	        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());  
	}

	public Map accountYJSumTotal(ReportQuery query) {
		// TODO Auto-generated method stub
		 StringBuffer sql2 = new StringBuffer();		 
	        sql2.append("select round(sum(ifnull(t.q1t,0)),2) q1t,round(sum(t.q1),2) q1,concat(round(round(sum(t.q1),2)/round(sum(ifnull(t.q1t,0)),2)*100,2),'%') q1r,round(sum(ifnull(t.q2t,0)),2) q2t,round(sum(t.q2),2) q2,concat(round(round(sum(t.q2),2)/round(sum(ifnull(t.q2t,0)),2)*100,2),'%') q2r,round(sum(ifnull(t.q3t,0)),2) q3t,round(sum(t.q3),2) q3,concat(round(round(sum(t.q3),2)/round(sum(ifnull(t.q3t,0)),2)*100,2),'%') q3r,round(sum(ifnull(t.q4t,0)),2) q4t,round(sum(t.q4),2) q4,concat(round(round(sum(t.q4),2)/round(sum(ifnull(t.q4t,0)),2)*100,2),'%') q4r,round(sum(ifnull(t.qat,0)),2) qat,round(sum(t.qa),2) qa,concat(round(round(sum(t.qa),2)/round(sum(ifnull(t.qat,0)),2)*100,2),'%') qar from ( ");
	        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
	        //sql2.append(" select a.id,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.q1,0),2) q1,round(ifnull(b.q2,0),2) q2,round(ifnull(b.q3,0),2) q3,round(ifnull(b.q4,0),2) q4,round(ifnull(b.qa,0),2) qa  from system_user a ");
	        sql2.append(" select a.id,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,j.conf1 q1t,round(ifnull(b.q1,0),2) q1,if(j.conf1=null,null,concat(round(round(ifnull(b.q1,0),2)/j.conf1*100,2),'%')) q1r,j.conf2 q2t,round(ifnull(b.q2,0),2) q2,if(j.conf2=null,null,concat(round(round(ifnull(b.q2,0),2)/j.conf2*100,2),'%')) q2r,j.conf3 q3t,round(ifnull(b.q3,0),2) q3,if(j.conf3=null,null,concat(round(round(ifnull(b.q3,0),2)/j.conf3*100,2),'%')) q3r,j.conf4 q4t,round(ifnull(b.q4,0),2) q4,if(j.conf4=null,null,concat(round(round(ifnull(b.q4,0),2)/j.conf4*100,2),'%')) q4r,j.conf5 qat,round(ifnull(b.qa,0),2) qa,if(j.conf5=null,null,concat(round(round(ifnull(b.qa,0),2)/j.conf5*100,2),'%')) qar  from system_user a ");
	        //Q1
	        
	        //account_ar
	        String curYear=query.getYear();
	        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
	        int inextYear=StringHelper.parseInt(curYear, 0)+1;
	        String nextYear=String.valueOf(inextYear);
	         
	        sql2.append(" left join ( ");
	        sql2.append(" select e.account, ");
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-01-01',if(f.send_dt<'").append(curYear).append("-04-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) q1, ");
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-04-01',if(f.send_dt<'").append(curYear).append("-07-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) q2, ");
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-07-01',if(f.send_dt<'").append(curYear).append("-10-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) q3, ");
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-10-01',if(f.send_dt<'").append(nextYear).append("-01-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) q4, ");
	        
	        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-01-01',if(f.send_dt<'").append(curYear).append("-04-01',ifnull(f.final_ar,0)*e.percent/100,0),0))  ");
	        sql2.append("+sum(if(f.send_dt>='").append(curYear).append("-04-01',if(f.send_dt<'").append(curYear).append("-07-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) ");
	        sql2.append("+sum(if(f.send_dt>='").append(curYear).append("-07-01',if(f.send_dt<'").append(curYear).append("-10-01',ifnull(f.final_ar,0)*e.percent/100,0),0))  ");
	        sql2.append("+sum(if(f.send_dt>='").append(curYear).append("-10-01',if(f.send_dt<'").append(nextYear).append("-01-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) qa ");
	        	        
	        
	        //sql2.append("  from  account_ar a ");
	        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
	        //sql2.append("  from  account_pn b ");
	        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
	        sql2.append("  from  contract_job_cv_offer c ");
	        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
	        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0))/100 rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
	        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
	        sql2.append("   inner join account_pn f on c.id=f.offer_id  ");
	        sql2.append(" group by  e.account ");
	        sql2.append(" ) b on b.account=a.account  ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
	        
	      //业绩指标
	        sql2.append(" left join  system_user_report_conf j  on a.account=j.account and j.type=3700 ");
	        
	        sql2.append(" ) t where 1=1 ");
	        List values = new ArrayList();
	       
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		        
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        }
	        
	        //return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());  
	        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
	        if (l!=null&&l.size()>0)
	            return (Map)l.get(0);
	        else return new HashMap();
	}
	
	public List workKpi(ReportQuery query) {
		// TODO Auto-generated method stub
		 StringBuffer sql2 = new StringBuffer();		
//			conf1:BD CALL数,
//			conf2:VISIT
//			conf3:联系人选数量
//			conf4:内部推荐,
//			conf5:顾问面试,
//			conf6:外部推荐,
//			conf7:客户面试
//			conf8:人选基本信息
//			conf9:人选简历		
		 
		 
		 //相乘系数
		  String xs="";
		  String xs2="";
		  int diff=DateHelper.getDiffDay(query.getBeginD(), query.getEndD());
		  if (diff>0) xs="*"+"(7/"+diff+")";
		  if (diff>0) xs2="*"+"("+diff+"/7)";
		  //sql2.append("select *").append(xs).append(" from ( ");
	        sql2.append("select * from ( ");
	        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
	        sql2.append(" select a.id,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,CEILING(m.conf1").append(xs2).append(") bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,if(m.conf1=null,null,concat(Convert(round(round(ifnull(b.bdCallCountFin,0),2)/m.conf1").append(xs).append("*100,2),char),'%')) bdCallCountFinRate,CEILING(m.conf2").append(xs2).append(") customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,if(m.conf2=null,null,concat(Convert(round(round(ifnull(c.customerVisitCountFin,0),2)/m.conf2").append(xs).append("*100,2),char),'%')) customerVisitCountFinRate,CEILING(m.conf5").append(xs2).append(") giCount,ifnull(d.giCountFin,0) giCountFin,if(m.conf5=null,null,concat(Convert(round(round(ifnull(d.giCountFin,0),2)/m.conf5").append(xs).append("*100,2),char),'%')) giCountFinRate,CEILING(m.conf6").append(xs2).append(") recomCount,ifnull(e.recomCountFin,0) recomCountFin,if(m.conf6=null,null,concat(Convert(round(round(ifnull(e.recomCountFin,0),2)/m.conf6").append(xs).append("*100,2),char),'%')) recomCountFinRate,CEILING(m.conf4").append(xs2).append(") recomCountIn,ifnull(e1.recomCountInFin,0) recomCountInFin,if(m.conf4=null,null,concat(Convert(round(round(ifnull(e1.recomCountInFin,0),2)/m.conf4").append(xs).append("*100,2),char),'%')) recomCountInFinRate,CEILING(m.conf9").append(xs2).append(") cvCount,ifnull(h1.cvCount,0) cvCountFin,if(m.conf9=null,null,concat(Convert(round(round(ifnull(h1.cvCount,0),2)/m.conf9").append(xs).append("*100,2),char),'%')) cvCountRate,CEILING(m.conf8").append(xs2).append(") cvBaseCount,ifnull(h.cvBaseCount,0) cvBaseCountFin,if(m.conf8=null,null,concat(Convert(round(round(ifnull(h.cvBaseCount,0),2)/m.conf8").append(xs).append("*100,2),char),'%')) cvBaseCountRate,CEILING(m.conf7").append(xs2).append(") kiCount,ifnull(k.kiCountFin,0) kiCountFin,if(m.conf7=null,null,concat(Convert(round(round(ifnull(k.kiCountFin,0),2)/m.conf7").append(xs).append("*100,2),char),'%')) kiCountFinRate,CEILING(m.conf3").append(xs2).append(") cvDayCount,ifnull(l.cvDayCountFin,0) cvDayCountFin,if(m.conf3=null,null,concat(Convert(round(round(ifnull(l.cvDayCountFin,0),2)/m.conf3").append(xs).append("*100,2),char),'%')) cvDayCountFinRate  from system_user a ");
	        sql2.append(" left join (select bd_account,count(*)  bdCallCountFin from   customer_crm where  crm_type='C' and crm_dt is not null and crm_status=1 and  crm_dt>=? and crm_dt<? group by bd_account) b on b.bd_account=a.account ");
	        sql2.append(" left join (select bd_account,count(*) customerVisitCountFin from   customer_crm where  crm_type='M' and crm_dt is not null and crm_status=1 and  crm_dt>=? and crm_dt<? group by bd_account) c on c.bd_account=a.account ");
	        sql2.append(" left join (select interviewer,count(distinct DATE_FORMAT(iv_dt,'%Y%m%d'),project_id,cv_id) giCountFin from  search_project_interview where  ifnull(result,0)<>-1 and iv_dt is not null and iv_dt>=? and iv_dt<? group by interviewer) d on d.interviewer=a.account ");
	        //sql2.append(" left join (select creator,count(*) recomCountFin from contract_job_cv_recom where  create_dt>=? and create_dt<? group by creator) e on e.creator=a.account ");
	        //sql2.append(" left join ( select aa.creator,count(distinct aa.cv_id) recomCountFin from (select creator,cv_id  from contract_job_cv_recom where  create_dt>=? and create_dt<? union all select creator,cv_id from contract_job_cv_jd where create_dt>=? and create_dt<? and type="+ContractJobCvJd.TYPE_JR+") aa group by aa.creator ) e on e.creator=a.account ");
	        //sql2.append(" left join ( select creator,count(distinct cv_id,job_id) recomCountFin from  contract_job_cv_recom  where is_email=1 and create_dt>=? and create_dt<?  group by creator ) e on e.creator=a.account ");
	        //sql2.append(" left join ( select creator,count(distinct cv_id,job_id) recomCountFin from  contract_job_cv_recom  where is_email=1 and create_dt>=? and create_dt<?  group by creator ) e on e.creator=a.account ");
	        sql2.append(" left join ( select creator,count(distinct cv_id,job_id) recomCountInFin from  contract_job_cv_recom  where  create_dt>=? and create_dt<?  group by creator ) e1 on e1.creator=a.account ");
	        sql2.append(" left join ( select creator,count(distinct cv_id,job_id) recomCountFin from contract_job_cv_jd where type=2 and create_dt>=? and create_dt<?	 group by creator ) e on e.creator=a.account ");	       	       
	        
	        //sql2.append(" left join (select b.creator,count(*) recomCountFin from contract_job_cv_recom a,contract_job_cv_offer b  where  b.create_dt>=? and b.create_dt<? and a.id=b.recom_id group by b.creator) f on f.creator=a.account ");
	        //sql2.append(" left join (select resume_informer,count(*) cvCount from cv  where cv_status='F' and   ifnull(resume_dt,create_dt)>=? and ifnull(resume_dt,create_dt)<? and cv_status='F' group by resume_informer) h on h.resume_informer=a.account ");
	        sql2.append(" left join (select creator resume_informer,count(*) cvBaseCount from cv  where   create_dt>=? and create_dt<? group by creator ) h on h.resume_informer=a.account ");
	        sql2.append(" left join (select ifnull(resume_informer,creator) resume_informer,sum(if(cv_status='F',1,0)) cvCount from cv  where   resume_dt>=? and resume_dt<? group by ifnull(resume_informer,creator) ) h1 on h1.resume_informer=a.account ");
	       // sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
	        sql2.append(" left join (select a.creator,count(distinct DATE_FORMAT(a.iv_dt,'%Y%m%d'),a.recom_id) kiCountFin from  contract_job_cv_interview a ,contract_job_cv_recom b where ifnull(a.result,-1)<>0 and b.id=a.recom_id and a.iv_dt<=now()  and   a.iv_dt>=? and a.iv_dt<? group by a.creator) k on k.creator=a.account ");
	        //联系人数量	        
	        sql2.append(" left join (select b.creator,count(distinct b.cv_id,date_format(b.create_dt,'%Y-%m-%d')) cvDayCountFin from search_project_cv_crm b where b.crm_dt is not null and length(b.comment)>0 and b.create_dt>=?  and b.create_dt<?  group by b.creator) l on l.creator=a.account ");
	        
	        //kpi指标
	        sql2.append(" left join  system_user_report_conf m  on a.account=m.account and m.type=3701 ");
	        
	        sql2.append(" ) t where 1=1 ");
	        List values = new ArrayList();
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
//	        values.add(query.getBeginD());
//	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	      //Business Assistant/Researcher()--R
			//Branch Manager,Consultant,Manager,GM-C
			//other -O
			//select * from system_role where name in('Business Assistant/Researcher','GM','Branch Manager','Consultant','Manager');
			if ("R".equals(query.getRoleType())) {
				sql2.append(" and exists(select 1 from system_user_roles a1 where a1.user_id=t.id and a1.role_id in(4)) ");			
			}else if ("C".equals(query.getRoleType())) {
				sql2.append(" and exists(select 1 from system_user_roles a1 where a1.user_id=t.id and a1.role_id in(2,5,6,8)) ");
			}else if ("O".equals(query.getRoleType())) {
				sql2.append(" and not exists(select 1 from system_user_roles a1 where a1.user_id=t.id and a1.role_id in(2,4,5,6,8)) ");
			}
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		        
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        }
	        
	        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());   
           
		//return null;
	}

	
	public Class getEntityClass() {
		// TODO Auto-generated method stub
		return null;
	}

	
	public List jobmonitor(ReportQuery query) {
		// TODO Auto-generated method stub
		 StringBuffer sql2 = new StringBuffer();		 
	        sql2.append("select * from ( ");
	        sql2.append(" select a.id,a.account,a.name,d.parent_ids,a.dept_id deptId,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,d.name deptName,'负责' jobRole,ifnull(b.jobActiveCount,0) jobActiveCount,ifnull(c.jobNewCount,0) jobNewCount  from system_user a ");
	        sql2.append(" left join (select b.account creator,count(*) jobActiveCount from   contract_job a,contract_job_partner b where  a.status in(0,1) and a.id=b.job_id and b.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("'  group by b.account) b on b.creator=a.account ");
	        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=c.job_id ");
	        //sql2.append(" left join (select creator,count(*) jobNewCount from   contract_job where  status<>3 and  create_dt>=? and create_dt<? group by creator) c on c.creator=a.account ");
	        sql2.append(" left join (select b.account creator,count(*) jobNewCount from   contract_job a,contract_job_partner b  where  a.status<>3 and a.id=b.job_id and b.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and  a.create_dt>=? and a.create_dt<? group by b.account) c on c.creator=a.account ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) d on d.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) d on d.user_id=a.id ");
	        sql2.append(" union all ");
	        sql2.append(" select id,a.account,a.name,d.parent_ids,a.dept_id deptId,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,d.name deptName,'合作' jobRole,ifnull(b.jobActiveCount,0) jobActiveCount,ifnull(c.jobNewCount,0) jobNewCount  from system_user a ");
	        sql2.append(" left join (select b.account creator,count(*) jobActiveCount from   contract_job a,contract_job_partner b where  a.status in(0,1) and a.id=b.job_id and b.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("'  group by b.account) b on b.creator=a.account ");
	        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=c.job_id ");
	        //sql2.append(" left join (select creator,count(*) jobNewCount from   contract_job where  status<>3 and  create_dt>=? and create_dt<? group by creator) c on c.creator=a.account ");
	        sql2.append(" left join (select b.account creator,count(*) jobNewCount from   contract_job a,contract_job_partner b  where  a.status<>3 and a.id=b.job_id and b.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and  a.create_dt>=? and a.create_dt<? group by b.account) c on c.creator=a.account ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) d on d.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) d on d.user_id=a.id ");
	        
	        sql2.append(" ) t where 1=1 ");
	        List values = new ArrayList();
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
	        if(isNotEmpty(query.getPartner())) {
		        sql2.append(" and  exists(select 1 from contract_job_partner aa where aa.account=t.account ) ");
		                
	        }
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		        
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        }else sql2.append(" order by t.account,t.jobRole");
	        
	        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());  
	}

	
	public Page jobeff(ReportQuery query) {		
		// TODO Auto-generated method stub
		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select * from ( ");
        sql2.append(" select a.id,c.abbr_name custName,c.name chName,c.full_name enName,a.name jobName,a.status jobStatus,j.deptName,a.creator,a.job_manager jobManager,a.create_dt createDt,DATEDIFF(now(),ifnull(a.open_d,a.create_dt)) jobdays,d.ondutyD,e.jdCount,f.recomCount,h.offerCount,h.estimateAr,i.cooperCount,floor(if(f.recomCount>0,ifnull(h.offerCount,0)/f.recomCount,0)*10000/100) offerrate  from contract_job a ");
        sql2.append(" inner join customer b on a.cust_id=b.id ");
        sql2.append(" inner join company c on b.company_id=c.id ");
        sql2.append(" left join (select b.job_id,min(a.checkin_d) ondutyD from  contract_job_cv_offer a ,contract_job_cv_recom b where b.id=a.recom_id and a.checkin_d is not null group by b.job_id) d on d.job_id=a.id ");
        sql2.append(" left join  (select job_id,count(distinct cv_id) jdCount from contract_job_cv_jd where type="+ContractJobCvJd.TYPE_JD+" group by job_id) e on a.id=e.job_id ");
        sql2.append(" left join  (select job_id,count(*) recomCount from contract_job_cv_recom where is_email=1 group by job_id) f on a.id=f.job_id ");
        //sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(t1.estimate_ar) estimateAr from contract_job_cv_offer t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) h on a.id=h.job_id ");
        sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(t3.final_ar) estimateAr from contract_job_cv_offer t1,contract_job_cv_recom t2,account_pn t3 where t1.recom_id=t2.id and t1.id=t3.offer_id  group by t2.job_id) h on a.id=h.job_id ");
        sql2.append(" left join  (select job_id,count(*) cooperCount from contract_job_partner group by job_id) i on a.id=i.job_id ");
        sql2.append(" left join  (select job_id,group_concat((cc.name) separator ',') deptName from contract_job_partner aa,system_user bb,system_branch_dept cc where aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and bb.dept_id=cc.id  group by job_id) j on a.id=j.job_id ");
        //system_branch_dept
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getJobName())) {
	        sql2.append(" and  t.jobName like ? ");
	        values.add("%"+query.getJobName()+"%");
	        
        }
        if(isNotEmpty(query.getCustName())) {
            sql2.append(" and  (t.custName like ? or t.chName like ? or t.enName like ? )");
	        values.add("%"+query.getCustName()+"%");
	        values.add("%"+query.getCustName()+"%");
	        values.add("%"+query.getCustName()+"%");
	        
        }
        if(isNotEmpty(query.getJobStatus())) {
	        sql2.append(" and  t.jobStatus = ? ");
	        values.add(query.getJobStatus());
	        
        }
        
        if(isNotEmpty(query.getPartner())) {
        	sql2.append(" and  exists(select 1 from contract_job_partner aa where t.id=aa.job_id and aa.account=? ) ");
	        values.add(query.getPartner());
	                
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc,system_user_title dd where dd.user_id=bb.id and dd.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		else sb.append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc,system_user_title dd where dd.user_id=bb.id and dd.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		
        		values.add("%/"+s[i]+"/%");	
        	}
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
//        if(isNotEmpty(query.getDeptId())) {	        
//	        sql2.append(" and  exists(select 1 from contract_job_partner aa,system_user bb where  aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and bb.dept_id=? ) ");
//	        values.add(query.getDeptId());
//        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(),"t.id", "t.creator"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        
        
        return super.getJdbcDao().createPage(sql2.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}
	

	
	public Page jobsum(ReportQuery query) {
		// TODO Auto-generated method stub
		/*
		 * 1、如果有收款记录，且有实际收款日期： dso=实际回款日期-预计收款日期
         * 2、如果没有收款记录，且offer没有实际上班日期，显示“没有实际上班日期”
         * 3、有实际上班日期，没有实际收款日期，显示“未回款”
		 */
		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select * from ( ");
        //sql2.append(" select a.id,c.name custName,a.name jobName,a.status,l.deptName,a.creator,a.job_manager jobManager,if(m.applyD is null,'没有实际上班日期',if(m.ondutyD is null,if(m.expectDt is null,'没有实际上班日期',if(m.arDt is null,'没有实际上班日期',m.dso)),if(m.expectDt is null,'没有实际上班日期',if(m.arDt is null,'未回款',m.dso)))) dso,DATEDIFF(ifnull(m.arDt,now()),ifnull(a.open_d,a.create_dt)) jobdays,a.create_dt createDt,d.ondutyD,a.offer_dt offerDt,ifnull(e.jdCount,0) jdCount,ifnull(f.recomCount,0) recomCount,if(ifnull(h.offerCount,0)>0,'成功',if(ifnull(j.interviewCount,0)>0,'面试中',if(ifnull(f.recomCount,0)>0,'推荐中','寻访中'))) jobStatus,ifnull(h.offerCount,0) offerCount,ifnull(h.estimateAr,0) estimateAr,ifnull(i.cooperCount,0) cooperCount,ifnull(k.workCount,0) workCount  from contract_job a ");
        //sql2.append(" select a.id,c.name custName,a.name jobName,a.status,l.deptName,a.creator,a.job_manager jobManager,if(m.applyD is null,'没有实际上班日期',if(m.ondutyD is null,if(m.expectDt is null,'没有实际上班日期',if(m.arDt is null,'没有实际上班日期',concat(m.dso,''))),if(m.expectDt is null,'没有实际上班日期',if(m.arDt is null,'未回款',concat(m.dso,''))))) dso,DATEDIFF(ifnull(m.arDt,now()),ifnull(a.open_d,a.create_dt)) jobdays,a.create_dt createDt,d.ondutyD,a.offer_dt offerDt,ifnull(e.jdCount,0) jdCount,ifnull(f.recomCount,0) recomCount,if(ifnull(h.offerCount,0)>0,'成功',if(ifnull(j.interviewCount,0)>0,'面试中',if(ifnull(f.recomCount,0)>0,'推荐中','寻访中'))) jobStatus,ifnull(h.offerCount,0) offerCount,ifnull(h.estimateAr,0) estimateAr,ifnull(i.cooperCount,0) cooperCount,ifnull(k.workCount,0) workCount  from contract_job a ");
        //1110222-没有实际上班日期;3330555-未回款        
        sql2.append(" select a.id,c.abbr_name custName,a.name jobName,a.status,l.deptName,a.creator,a.job_manager jobManager,if(m.applyD is null,1110222,if(m.checkinD is null,if(m.expectDt is null,3330555,if(m.arDt is null,3330555,1110222)),if(m.expectDt is null,1110222,if(m.arDt is null,3330555,m.dso)))) dso,DATEDIFF(ifnull(m.arDt,now()),ifnull(a.open_d,a.create_dt)) jobdays,a.create_dt createDt,d.ondutyD,a.offer_dt offerDt,ifnull(e.jdCount,0) jdCount,ifnull(f.recomCount,0) recomCount,if(ifnull(h.offerCount,0)>0,if(n.succStatus=1,if(n.final_ar=n.arAmount,'成功','已经上班'),'offer'),if(ifnull(j.interviewCount,0)>0,'面试中',if(ifnull(f.recomCount,0)>0,'推荐中','寻访中'))) jobStatus,ifnull(h.offerCount,0) offerCount,ifnull(h.estimateAr,0) estimateAr,ifnull(i.cooperCount,0) cooperCount,ifnull(k.workCount,0) workCount  from contract_job a ");
        //sql2.append(" select a.id,c.name custName,a.name jobName,a.status,l.deptName,a.creator,a.job_manager jobManager,if(m.arDt is null,1000,m.dso) dso,DATEDIFF(ifnull(m.arDt,now()),ifnull(a.open_d,a.create_dt)) jobdays,a.create_dt createDt,d.ondutyD,a.offer_dt offerDt,ifnull(e.jdCount,0) jdCount,ifnull(f.recomCount,0) recomCount,if(ifnull(h.offerCount,0)>0,'成功',if(ifnull(j.interviewCount,0)>0,'面试中',if(ifnull(f.recomCount,0)>0,'推荐中','寻访中'))) jobStatus,ifnull(h.offerCount,0) offerCount,ifnull(h.estimateAr,0) estimateAr,ifnull(i.cooperCount,0) cooperCount,ifnull(k.workCount,0) workCount  from contract_job a ");
        sql2.append(" inner join customer b on a.cust_id=b.id ");
        sql2.append(" inner join company c on b.company_id=c.id ");
        sql2.append(" left join (select b.job_id,min(a.checkin_d) ondutyD from  contract_job_cv_offer a ,contract_job_cv_recom b where b.id=a.recom_id and a.checkin_d is not null group by b.job_id) d on d.job_id=a.id ");
        sql2.append(" left join  (select job_id,count(distinct cv_id) jdCount from contract_job_cv_jd where type="+ContractJobCvJd.TYPE_JD+" group by job_id) e on a.id=e.job_id ");
        sql2.append(" left join  (select job_id,count(*) recomCount from contract_job_cv_recom where is_email=1 group by job_id) f on a.id=f.job_id ");
        //业绩金额
        sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(t3.final_ar) estimateAr from contract_job_cv_offer t1,contract_job_cv_recom t2,account_pn t3 where t1.recom_id=t2.id and t1.id=t3.offer_id  group by t2.job_id) h on a.id=h.job_id ");
        sql2.append(" left join  (select job_id,count(*) cooperCount from contract_job_partner group by job_id) i on a.id=i.job_id ");
        sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) interviewCount from contract_job_cv_interview t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) j on a.id=j.job_id ");
        sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) workCount from contract_job_cv_crm t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) k on a.id=k.job_id ");
        sql2.append(" left join  (select job_id,group_concat((cc.name) separator ',') deptName from contract_job_partner aa,system_user bb,system_branch_dept cc where aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and bb.dept_id=cc.id  group by job_id) l on a.id=l.job_id ");
        sql2.append(" left join  (select d.job_id,min(ifnull(DATEDIFF(a.ar_dt,c.checkin_d),0)) dso,min(a.expect_dt) expectDt,min(a.ar_dt) arDt,min(c.apply_d) applyD,min(c.checkin_d) checkinD from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id group by d.job_id) m on a.id=m.job_id ");
        
        sql2.append(" left join  (select t2.job_id,min(if(ifnull(t1.status,0)=1,1,0)) succStatus,sum(t3.final_ar) final_ar,sum(t4.arAmount) arAmount from contract_job_cv_offer t1 inner join contract_job_cv_recom t2 on t1.recom_id=t2.id left join account_pn t3 on t1.id=t3.offer_id left join (select t3.offer_id,sum(t4.amount) arAmount from account_pn t3 left join account_ar t4 on t3.nbr=t4.pn_nbr group by t3.offer_id) t4 on t1.id=t4.offer_id group by t2.job_id) n on a.id=n.job_id ");
        
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getJobName())) {
	        sql2.append(" and  t.jobName like ? ");
	        values.add("%"+query.getJobName()+"%");
	        
        }
        if(isNotEmpty(query.getCustName())) {
	        sql2.append(" and  t.custName like ? ");
	        values.add("%"+query.getCustName()+"%");
	        
        }
        if(isNotEmpty(query.getPartner())) {
	        sql2.append(" and  exists(select 1 from contract_job_partner aa where t.id=aa.job_id and aa.account=? ) ");
	        values.add(query.getPartner());
	                
        }
        if(isNotEmpty(query.getJobStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getJobStatus());
	        
        }
//        if(isNotEmpty(query.getDeptId())) {	        
//	        sql2.append(" and  exists(select 1 from contract_job_partner aa,system_user bb where aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and bb.dept_id=? ) ");
//	        values.add(query.getDeptId());
//        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		//if(sb.length()>0) sb.append(" or ").append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc where bb.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		//else sb.append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc where bb.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		if(sb.length()>0) sb.append(" or ").append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc,system_user_title dd where dd.user_id=bb.id and dd.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		else sb.append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc,system_user_title dd where dd.user_id=bb.id and dd.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		values.add("%/"+s[i]+"/%");	
        	}
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(),"t.id", "t.creator"));
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        
        
        return super.getJdbcDao().createPage(sql2.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Map jobsumTotal(ReportQuery query) {
		// TODO Auto-generated method stub
		StringBuffer sql2 = new StringBuffer();			
        sql2.append("select round(sum(if(t.dso=-99999,0,t.dso))/sum(if(t.dso=-99999,0,1)),2) dso,round(sum(t.jobdays)/count(t.id),2) jobdays,round(sum(t.jdCount)/count(t.id),2) jdCount,round(sum(t.recomCount)/count(t.id),2) recomCount,round(sum(t.offerCount)/count(t.id),2) offerCount,round(sum(t.estimateAr)/count(t.id),2) estimateAr,round(sum(t.workCount)/count(t.id),2) workCount from ( ");
        //sql2.append(" select sum(DATEDIFF(m.confirmDt,m.sendDt))/count(a.id) dso,sum(DATEDIFF(m.confirmDt,a.create_dt))/count(a.id) jobdays,sum(e.jdCount) jdCount,sum(f.recomCount) recomCount,sum(h.offerCount) offerCount,sum(h.estimateAr) estimateAr,sum(k.workCount) workCount  from contract_job a ");
        sql2.append(" select a.id,c.abbr_name custName,a.name jobName,a.status,l.deptName,a.creator,a.job_manager jobManager,ifnull(m.dso,-99999) dso,DATEDIFF(ifnull(m.arDt,now()),ifnull(a.open_d,a.create_dt)) jobdays,a.create_dt createDt,d.ondutyD,a.offer_dt offerDt,ifnull(e.jdCount,0) jdCount,ifnull(f.recomCount,0) recomCount,if(ifnull(h.offerCount,0)>0,'成功',if(ifnull(j.interviewCount,0)>0,'面试中',if(ifnull(f.recomCount,0)>0,'推荐中','寻访中'))) jobStatus,ifnull(h.offerCount,0) offerCount,ifnull(h.estimateAr,0) estimateAr,ifnull(i.cooperCount,0) cooperCount,ifnull(k.workCount,0) workCount  from contract_job a ");
        sql2.append(" inner join customer b on a.cust_id=b.id ");
        sql2.append(" inner join company c on b.company_id=c.id ");
        sql2.append(" left join (select b.job_id,min(a.checkin_d) ondutyD from  contract_job_cv_offer a ,contract_job_cv_recom b where b.id=a.recom_id and a.checkin_d is not null group by b.job_id) d on d.job_id=a.id ");
        sql2.append(" left join  (select job_id,count(distinct cv_id) jdCount from contract_job_cv_jd where type="+ContractJobCvJd.TYPE_JD+" group by job_id) e on a.id=e.job_id ");
        sql2.append(" left join  (select job_id,count(*) recomCount from contract_job_cv_recom where is_email=1 group by job_id) f on a.id=f.job_id ");
        //sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(t1.estimate_ar) estimateAr from contract_job_cv_offer t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) h on a.id=h.job_id ");
        sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(t3.final_ar) estimateAr from contract_job_cv_offer t1,contract_job_cv_recom t2,account_pn t3 where t1.recom_id=t2.id and t1.id=t3.offer_id  group by t2.job_id) h on a.id=h.job_id ");
        sql2.append(" left join  (select job_id,count(*) cooperCount from contract_job_partner group by job_id) i on a.id=i.job_id ");
        sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) interviewCount from contract_job_cv_interview t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) j on a.id=j.job_id ");
        sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) workCount from contract_job_cv_crm t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) k on a.id=k.job_id ");
        sql2.append(" left join  (select job_id,group_concat((cc.name) separator ',') deptName from contract_job_partner aa,system_user bb,system_branch_dept cc where aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and bb.dept_id=cc.id  group by job_id) l on a.id=l.job_id ");
        //sql2.append(" left join  (select d.job_id,min(b.send_dt) sendDt,min(a.confirm_dt) confirmDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id group by d.job_id) m on a.id=m.job_id ");
        sql2.append(" left join  (select d.job_id,min(ifnull(DATEDIFF(a.ar_dt,c.checkin_d),-99999)) dso,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id group by d.job_id) m on a.id=m.job_id ");
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getJobName())) {
	        sql2.append(" and  t.jobName like ? ");
	        values.add("%"+query.getJobName()+"%");
	        
        }
        if(isNotEmpty(query.getCustName())) {
	        sql2.append(" and  t.custName like ? ");
	        values.add("%"+query.getCustName()+"%");
	        
        }
        if(isNotEmpty(query.getPartner())) {
	        sql2.append(" and  exists(select 1 from contract_job_partner aa where t.id=aa.job_id and aa.account=? ) ");
	        values.add(query.getPartner());
	                
        }
        if(isNotEmpty(query.getJobStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getJobStatus());
	        
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		//if(sb.length()>0) sb.append(" or ").append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc where bb.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		//else sb.append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc where bb.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		if(sb.length()>0) sb.append(" or ").append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc,system_user_title dd where dd.user_id=bb.id and dd.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		else sb.append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc,system_user_title dd where dd.user_id=bb.id and dd.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		values.add("%/"+s[i]+"/%");	
        	}
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(),"t.id", "t.creator"));
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        
        
        
        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
        if (l!=null&&l.size()>0)
            return (Map)l.get(0);
        else return new HashMap();
	}
	public List myyj(ReportQuery query) {
		// TODO Auto-generated method stub
		//个人业绩总结
		// TODO Auto-generated method stub
		 StringBuffer sql2 = new StringBuffer();		 
	        sql2.append("select * from ( ");
	        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
	        sql2.append(" select concat(a.id,b.offer_id) id,b.id jobId,b.jobName,b.cvName,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,date_format(b.applyD,'%Y-%m-%d') applyD,date_format(b.sendDt,'%Y-%m-%d') sendDt,date_format(h.confirmDt,'%Y-%m-%d') confirmDt  from system_user a ");
	        //Q1
	        //account_ar
	        String curYear=query.getYear();
	        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
	        int inextYear=StringHelper.parseInt(curYear, 0)+1;
	        String nextYear=String.valueOf(inextYear);
	        List values = new ArrayList();
	        sql2.append(" left join ( ");
	        sql2.append(" select e.account,h.id,c.id offer_id,h.name jobName,i.cv_name cvName,k.abbr_name custName, ");
	        sql2.append(" sum(ifnull(b.final_ar,0)) yjamount,sum(ifnull(b.final_ar,0)*ifnull(e.percent,0)/100) yjrate,max(c.apply_d) applyD,min(b.send_dt) sendDt ");
	        
	        	        
	        
	        //sql2.append("  from  account_ar a ");
	        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
	        //sql2.append("  from  account_pn b ");	        
	        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
	        sql2.append("  from  contract_job_cv_offer c ");
	        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
	        sql2.append(" left join account_pn b on  c.id=b.offer_id ");
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where b.account=? and a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	       // values.add(query.getAccount());
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	        //sql2.append(" left join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
	        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
	        sql2.append(" inner join contract_job h on h.id=d.job_id ");
	        sql2.append(" inner join cv i on i.id=d.cv_id ");
	        sql2.append(" inner join customer j on j.id=h.cust_id ");
	        sql2.append(" inner join company k on k.id=j.company_id ");
	        
	        sql2.append(" where  b.send_dt>=? and b.send_dt<? ");
	        //sql2.append(" where  c.apply_d>=? and c.apply_d<? ");
	        sql2.append(" group by  e.account,h.id,c.id ");
	        sql2.append(" ) b on b.account=a.account    ");
	        //sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' and   e.create_dt>=? and e.create_dt<?  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_offer_partner f where c.id=f.offer_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("'  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
	        sql2.append(" ) t where 1=1 and t.yj>0 ");
	       
	       // values.add(query.getBeginD());
	        //values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        //values.add(new Date());
	        //values.add(new Date());
	        
		    //sql2.append(" and  exists (select 1 from contract_job_partner bb where bb.job_id=t.jobId and bb.account=?  ) ");
		    //values.add(query.getAccount());
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
		    
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        }
	        logger.info("业绩监控SQL:"+sql2.toString());
	        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());  
	        //logger.info("业绩监控SQL:"+sql2.toString());
	}

	public Map myyjTotal(ReportQuery query) {
		// TODO Auto-generated method stub
		//个人业绩总结
		// TODO Auto-generated method stub
		 StringBuffer sql2 = new StringBuffer();		 
	        sql2.append("select count(distinct t.jobId) jobId,sum(t.yjrate) yjrate from ( ");
	        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
	        //sql2.append(" select count(distinct b.id) jobId,sum(yjrate) yjrate  from system_user a ");
	        sql2.append(" select a.id,b.id jobId,b.jobName,b.cvName,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,b.applyD,b.sendDt,h.confirmDt  from system_user a ");
	        //Q1
	        //account_ar
	        String curYear=query.getYear();
	        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
	        int inextYear=StringHelper.parseInt(curYear, 0)+1;
	        String nextYear=String.valueOf(inextYear);
	        List values = new ArrayList();
	        sql2.append(" left join ( ");
	        sql2.append(" select e.account,h.id,c.id offer_id,i.id cv_id,h.name jobName,i.cv_name cvName,k.abbr_name custName, ");
	        sql2.append(" sum(ifnull(b.final_ar,0)) yjamount,sum(ifnull(b.final_ar,0)*ifnull(e.percent,0)/100) yjrate,max(c.apply_d) applyD,min(b.send_dt) sendDt ");
	        
	        	        
	        
	        //sql2.append("  from  account_ar a ");
	        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
	        //sql2.append("  from  account_pn b ");
	        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
	        sql2.append("  from  contract_job_cv_offer c ");
	        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
	        sql2.append(" left join account_pn b on  c.id=b.offer_id ");
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where b.account=? and a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	        //values.add(query.getAccount());
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	        //sql2.append(" left join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
	        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
	        sql2.append(" inner join contract_job h on h.id=d.job_id ");
	        sql2.append(" inner join cv i on i.id=d.cv_id ");
	        sql2.append(" inner join customer j on j.id=h.cust_id ");
	        sql2.append(" inner join company k on k.id=j.company_id ");
	        
	        sql2.append(" where  b.send_dt>=? and b.send_dt<? ");
	        //sql2.append(" where  c.apply_d>=? and c.apply_d<? ");
	        sql2.append(" group by  e.account,h.id,c.id  ");
	        sql2.append(" ) b on b.account=a.account    ");
	        //sql2.append(" left join  (select c.creator,d.job_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where   c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and   e.create_dt>=? and e.create_dt<?  group by c.creator,d.job_id) h on   h.job_id=b.id and h.creator=a.account ");
	        //sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' and   e.create_dt>=? and e.create_dt<?  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_offer_partner f where c.id=f.offer_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("'  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
	        sql2.append(" ) t where 1=1 and t.yj>0 ");
	       
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        //values.add(query.getBeginD());
	        //values.add(query.getEndD());
	        //values.add(new Date());
	        //values.add(new Date());
	        
		    //sql2.append(" and  exists (select 1 from contract_job_partner bb where bb.job_id=t.jobId and bb.account=?  ) ");
		    //values.add(query.getAccount());
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	        
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        }
	        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
	        if (l!=null&&l.size()>0)
	            return (Map)l.get(0);
	        else return new HashMap();
	}
	public List myofferyj(ReportQuery query) {
		// TODO Auto-generated method stub
		//个人业绩总结
		// TODO Auto-generated method stub
		 StringBuffer sql2 = new StringBuffer();		 
	        sql2.append("select * from ( ");
	        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
	        sql2.append(" select b.offer_id id,b.id jobId,b.jobName,b.offer_id offerId,b.cvName,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,date_format(b.applyD,'%Y-%m-%d') applyD,date_format(b.sendDt,'%Y-%m-%d') sendDt,date_format(h.confirmDt,'%Y-%m-%d') confirmDt  from system_user a ");
	        //Q1
	        //account_ar
	        //account_pn.final_ar offer收款金额
	      //contract_job_cv_offer.estimate_ar offer预计收费
	        String curYear=query.getYear();
	        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
	        int inextYear=StringHelper.parseInt(curYear, 0)+1;
	        String nextYear=String.valueOf(inextYear);
	        List values = new ArrayList();
	        sql2.append(" left join ( ");
	        sql2.append(" select e.account,h.id,c.id offer_id,h.name jobName,i.cv_name cvName,k.abbr_name custName, ");
	        sql2.append(" sum(ifnull(c.estimate_ar,0)) yjamount,sum(ifnull(c.estimate_ar,0)*ifnull(e.percent,0)/100) yjrate,max(c.apply_d) applyD,min(b.send_dt) sendDt ");
	        
	        	        
	        
	        //sql2.append("  from  account_ar a ");
	        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
	        //sql2.append("  from  account_pn b ");	        
	        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
	        sql2.append("  from  contract_job_cv_offer c ");
	        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
	        sql2.append(" left join account_pn b on  c.id=b.offer_id ");
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where b.account=? and a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	       // values.add(query.getAccount());
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	        //sql2.append(" left join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
	        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
	        sql2.append(" inner join contract_job h on h.id=d.job_id ");
	        sql2.append(" inner join cv i on i.id=d.cv_id ");
	        sql2.append(" inner join customer j on j.id=h.cust_id ");
	        sql2.append(" inner join company k on k.id=j.company_id ");
	        
	        //sql2.append(" where  b.send_dt>=? and b.send_dt<? ");	        
	        sql2.append(" where  c.apply_d>=? and c.apply_d<? ");
	        
	      //已经有“发账单日期”的offer就不在这边表中统计
	        sql2.append(" and not exists(select 1 from account_pn h1 where h1.offer_id=c.id and h1.send_dt is not null ) ");
	        
	        sql2.append(" group by  e.account,h.id,c.id ");
	        sql2.append(" ) b on b.account=a.account    ");
	        //sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' and   e.create_dt>=? and e.create_dt<?  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("'  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
	        sql2.append(" ) t where 1=1 and t.yj>0 ");
	       
	       // values.add(query.getBeginD());
	        //values.add(query.getEndD());
	        
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        //values.add(new Date());
	        //values.add(new Date());
	        
		    //sql2.append(" and  exists (select 1 from contract_job_partner bb where bb.job_id=t.jobId and bb.account=?  ) ");
		    //values.add(query.getAccount());
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
		    
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        //排重
	        sql2.append(" group by t.id  ");
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        }
	        logger.info("业绩监控SQL:"+sql2.toString());
	        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());  
	        //logger.info("业绩监控SQL:"+sql2.toString());
	}
	public Map myofferyjTotal(ReportQuery query) {
		// TODO Auto-generated method stub
		//个人业绩总结
		// TODO Auto-generated method stub
		 StringBuffer sql2 = new StringBuffer();		 
	        sql2.append("select count(distinct t.jobId) jobId,sum(t.yjrate) yjrate from ( ");
	        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
	        //sql2.append(" select count(distinct b.id) jobId,sum(yjrate) yjrate  from system_user a ");
	        sql2.append(" select a.id,b.id jobId,b.jobName,b.cvName,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,b.applyD,b.sendDt,h.confirmDt  from system_user a ");
	        //Q1
	        //account_ar
	        String curYear=query.getYear();
	        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
	        int inextYear=StringHelper.parseInt(curYear, 0)+1;
	        String nextYear=String.valueOf(inextYear);
	        List values = new ArrayList();
	        sql2.append(" left join ( ");
	        sql2.append(" select e.account,h.id,c.id offer_id,i.id cv_id,h.name jobName,i.cv_name cvName,k.abbr_name custName, ");
	        sql2.append(" sum(ifnull(c.estimate_ar,0)) yjamount,sum(ifnull(c.estimate_ar,0)*ifnull(e.percent,0)/100) yjrate,max(c.apply_d) applyD,min(b.send_dt) sendDt ");
	        
	        	        
	        
	        //sql2.append("  from  account_ar a ");
	        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
	        //sql2.append("  from  account_pn b ");
	        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
	        sql2.append("  from  contract_job_cv_offer c ");
	        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
	        sql2.append(" left join account_pn b on  c.id=b.offer_id ");
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where b.account=? and a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	        //values.add(query.getAccount());
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	        //sql2.append(" left join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
	        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
	        sql2.append(" inner join contract_job h on h.id=d.job_id ");
	        sql2.append(" inner join cv i on i.id=d.cv_id ");
	        sql2.append(" inner join customer j on j.id=h.cust_id ");
	        sql2.append(" inner join company k on k.id=j.company_id ");
	        
	       // sql2.append(" where  b.send_dt>=? and b.send_dt<? ");
	        sql2.append(" where  c.apply_d>=? and c.apply_d<? ");
	      //已经有“发账单日期”的offer就不在这边表中统计
	        sql2.append(" and not exists(select 1 from account_pn h1 where h1.offer_id=c.id and h1.send_dt is not null ) ");

	        sql2.append(" group by  e.account,h.id,c.id  ");
	        sql2.append(" ) b on b.account=a.account    ");
	        //sql2.append(" left join  (select c.creator,d.job_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where   c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and   e.create_dt>=? and e.create_dt<?  group by c.creator,d.job_id) h on   h.job_id=b.id and h.creator=a.account ");
	        sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' and   e.create_dt>=? and e.create_dt<?  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
	        sql2.append(" ) t where 1=1 and t.yj>0 ");
	       
	        
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        //values.add(new Date());
	        //values.add(new Date());
	        
		    //sql2.append(" and  exists (select 1 from contract_job_partner bb where bb.job_id=t.jobId and bb.account=?  ) ");
		    //values.add(query.getAccount());
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	        
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        }
	        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
	        if (l!=null&&l.size()>0)
	            return (Map)l.get(0);
	        else return new HashMap();
	}
	public List bajobsum(ReportQuery query) {
		// TODO Auto-generated method stub
		StringBuffer sql2 = new StringBuffer();
		List values = new ArrayList();
		//姓名	部门	岗位	活跃职位名	客户简称	职位负责人	新增CC人选	新增简历人选	新增联系记录	被顾问面试人选数	被客户面试人选数	被OFFER人选数	成功上班人选数	辅助成功职位金额
        sql2.append("select * from ( ");
        sql2.append(" select a.id,c1.account,c.abbr_name custName,j.parent_ids,m.status,a.name jobName,a.status jobStatus,j.name deptName,l.name titleName,a.creator,a.job_manager jobManager,a.create_dt createDt,ifnull(f.ccCount,0) ccCount,ifnull(h.cvCount,0) cvCount,ifnull(t3.cvContactCount,0) cvContactCount,ifnull(r1.recomCount,0) recomCount,ifnull(d.offerCount,0) offerCount,ifnull(d.dutyCount,0) dutyCount,ifnull(e.giCount,0) giCount,ifnull(k.kiCount,0) kiCount,ifnull(i.assistAmount,0) assistAmount  from contract_job a ");
        sql2.append(" inner join customer b on a.cust_id=b.id ");
        sql2.append(" inner join company c on b.company_id=c.id ");
        sql2.append(" inner join contract_job_partner c1 on c1.job_id=a.id and c1.role='").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' ");
        //sql2.append(" left join (select b.job_id,a.creator,count(*) offerCount,sum(if(a.status=1,1,0)) dutyCount from  contract_job_cv_offer a ,contract_job_cv_recom b where b.id=a.recom_id and a.checkin_d is not null and   a.create_dt>=? and a.create_dt<? group by b.job_id,a.creator) d on d.job_id=a.id and d.creator=c1.account ");
        sql2.append(" left join (select b.job_id,c.account,count(distinct a.id) offerCount,sum(if(a.status=1,1,0)) dutyCount from  contract_job_cv_offer a ,contract_job_cv_recom b,contract_job_offer_partner c where b.id=a.recom_id and c.offer_id=a.id and a.checkin_d is not null and   a.create_dt>=? and a.create_dt<? group by b.job_id,c.account) d on d.job_id=a.id and d.account=c1.account ");
        
        sql2.append(" left join  (select  a.job_id,b.creator,count(distinct b.ref_cvid) ccCount from contract_job_search_project a,search_project_cc_list b where a.project_id=b.project_id and   b.create_dt>=? and b.create_dt<? group by a.job_id,a.creator) f on a.id=f.job_id and f.creator=c1.account ");
        sql2.append(" left join  (select  a.job_id,b.creator,count(distinct b.cv_id) cvCount from contract_job_search_project a,search_project_cv b,cv c where a.project_id=b.project_id and c.id=b.cv_id and c.cv_status='F' and   ifnull(c.resume_dt,c.create_dt)>=? and ifnull(c.resume_dt,c.create_dt)<? group by a.job_id,b.creator) h on a.id=h.job_id and h.creator=c1.account ");
        sql2.append(" left join ( select aa.creator,aa.job_id,count(distinct aa.cv_id) cvContactCount from ( ");		
		sql2.append("       select  a.ref_cvid cv_id,a.creator,b.job_id from search_project_cc_list a,contract_job_search_project b where a.project_id=b.project_id and   a.create_dt>=? and a.create_dt<? ");
		sql2.append("       union all ");
		sql2.append("       select  a.cv_id,a.creator,b.job_id from search_project_cv_crm a ,contract_job_search_project b where a.project_id=b.project_id and   a.create_dt>=? and a.create_dt<?  ");		
		sql2.append("     ) aa group by aa.creator,aa.job_id ) t3 on t3.job_id=a.id and t3.creator=c1.account      ");
		//内部推荐数
		sql2.append(" left join  (select job_id,creator,count(*) recomCount from contract_job_cv_recom where create_dt>=? and create_dt<?  group by job_id,creator) r1 on a.id=r1.job_id and r1.creator=c1.account ");
        sql2.append(" left join  (select  a.job_id,b.creator,count(distinct b.cv_id) giCount from contract_job_search_project a,search_project_interview b where a.project_id=b.project_id and   b.create_dt>=? and b.create_dt<?  group by a.job_id,b.creator) e on a.id=e.job_id and e.creator=c1.account ");
        sql2.append(" left join (select b.job_id,a.creator,count(*) kiCount from  contract_job_cv_interview a ,contract_job_cv_recom b where b.id=a.recom_id  and   a.create_dt>=? and a.create_dt<? group by b.job_id,a.creator) k on k.job_id=a.id and k.creator=c1.account ");
        //sql2.append(" left join  (select job_id,count(*) cooperCount from contract_job_partner group by job_id) i on a.id=i.job_id ");
        //sql2.append(" left join  (select job_id,group_concat((cc.name) separator ',') jobManager from contract_job_partner aa,system_user bb,system_branch_dept cc where aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and bb.dept_id=cc.id  group by job_id) j on a.id=j.job_id ");
        //sql2.append(" left join  (select job_id,c.creator,sum(ifnull(c.estimate_ar,0)) assistAmount from contract_job_cv_offer c,contract_job_cv_recom d where    d.id=c.recom_id and   c.create_dt>=? and c.create_dt<?  group by job_id,c.creator) i on a.id=i.job_id and  i.creator=c1.account ");
        sql2.append(" left join  ( ");
         sql2.append(" select a.id,e.account,sum(ifnull(f.final_ar*e.percent/100,0)) assistAmount from  contract_job a ");        
        sql2.append("   inner join contract_job_cv_recom c on a.id=c.job_id  ");
        sql2.append("   inner join contract_job_cv_offer d on d.recom_id=c.id  ");
        sql2.append("   inner join contract_job_offer_partner e on d.id=e.offer_id and e.role='BA' ");
        sql2.append("   inner join account_pn f on d.id=f.offer_id  ");
        sql2.append("   where  f.send_dt>=? and f.send_dt<? ");
        sql2.append("   group by e.account,a.id  ");
        sql2.append(" ) i on a.id=i.id and  i.account=c1.account  ");
        
        sql2.append(" left join (select id,account,status,dept_id,title_id,name from system_user) m on m.account=c1.account ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) j on j.id=m.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) j on j.user_id=m.id ");
        //sql2.append(" left join (select id,name from system_branch_title) l on l.id=m.title_id ");
        sql2.append(" left join (select b2.user_id,group_concat((b3.name) separator ',') name from system_user_title b2,system_branch_title b3 where  b2.title_id=b3.id  group by b2.user_id) l on l.user_id=m.id ");
        //system_branch_dept
        //sql2.append(" where a.status in(0,1)  ) t where 1=1 ");
        sql2.append(" where 1=1 ");
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        if(isNotEmpty(query.getJobStatus())) {
	        sql2.append(" and  a.status = ? ");
	        values.add(query.getJobStatus());
	        //System.out.println(" and  a.status = ? "+"--"+query.getJobStatus());
        }
        sql2.append(" ) t where 1=1 ");
        
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        
        
        
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getJobName())) {
	        sql2.append(" and  t.jobName like ? ");
	        values.add("%"+query.getJobName()+"%");
	        
        }
        if(isNotEmpty(query.getCustName())) {
	        sql2.append(" and  t.custName like ? ");
	        values.add("%"+query.getCustName()+"%");
	        
        }
        if(isNotEmpty(query.getJobStatus())) {
	        sql2.append(" and  t.jobStatus = ? ");
	        values.add(query.getJobStatus());
	        
        }
        
        if(isNotEmpty(query.getPartner())) {
        	sql2.append(" and  exists(select 1 from contract_job_partner aa where t.id=aa.job_id and aa.account=? ) ");
	        values.add(query.getPartner());
	                
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		        	
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authBAReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }else sql2.append(" order by t.account ");
        
        
        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());	
	}

	public Map bajobsumTotal(ReportQuery query) {
		// TODO Auto-generated method stub
		StringBuffer sql2 = new StringBuffer();	
		List values = new ArrayList();
		//姓名	部门	岗位	活跃职位名	客户简称	职位负责人	新增CC人选	新增简历人选	新增联系记录	被顾问面试人选数	被客户面试人选数	被OFFER人选数	成功上班人选数	辅助成功职位金额
        sql2.append("select sum(t.ccCount) ccCount,sum(t.cvCount) cvCount,sum(t.cvContactCount) cvContactCount,sum(t.recomCount) recomCount,sum(t.offerCount) offerCount,sum(t.dutyCount) dutyCount,sum(t.giCount) giCount,sum(t.kiCount) kiCount,sum(t.assistAmount) assistAmount from ( ");
        //sql2.append(" select sum(f.ccCount) ccCount,sum(h.cvCount) cvCount,sum(t3.cvContactCount) cvContactCount,sum(d.offerCount) offerCount,sum(d.dutyCount) dutyCount,sum(e.giCount) giCount,sum(k.kiCount) kiCount,sum(i.assistAmount) assistAmount  from contract_job a ");
        sql2.append(" select a.id,c1.account,c.abbr_name custName,j.parent_ids,m.status,a.name jobName,a.status jobStatus,j.name deptName,l.name titleName,a.creator,a.job_manager jobManager,a.create_dt createDt,ifnull(f.ccCount,0) ccCount,ifnull(h.cvCount,0) cvCount,ifnull(t3.cvContactCount,0) cvContactCount,ifnull(r1.recomCount,0) recomCount,ifnull(d.offerCount,0) offerCount,ifnull(d.dutyCount,0) dutyCount,ifnull(e.giCount,0) giCount,ifnull(k.kiCount,0) kiCount,ifnull(i.assistAmount,0) assistAmount  from contract_job a ");
        sql2.append(" inner join customer b on a.cust_id=b.id ");
        sql2.append(" inner join company c on b.company_id=c.id ");
        sql2.append(" inner join contract_job_partner c1 on c1.job_id=a.id and c1.role='").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' ");
        sql2.append(" left join (select b.job_id,c.account,count(distinct a.id) offerCount,sum(if(a.status=1,1,0)) dutyCount from  contract_job_cv_offer a ,contract_job_cv_recom b,contract_job_offer_partner c where b.id=a.recom_id and c.offer_id=a.id and a.checkin_d is not null and   a.create_dt>=? and a.create_dt<? group by b.job_id,c.account) d on d.job_id=a.id and d.account=c1.account ");
                
        sql2.append(" left join  (select  a.job_id,b.creator,count(distinct b.ref_cvid) ccCount from contract_job_search_project a,search_project_cc_list b where a.project_id=b.project_id and   b.create_dt>=? and b.create_dt<? group by a.job_id,a.creator) f on a.id=f.job_id and f.creator=c1.account ");
        sql2.append(" left join  (select  a.job_id,b.creator,count(distinct b.cv_id) cvCount from contract_job_search_project a,search_project_cv b,cv c where a.project_id=b.project_id and c.id=b.cv_id and c.cv_status='F' and   ifnull(c.resume_dt,c.create_dt)>=? and ifnull(c.resume_dt,c.create_dt)<? group by a.job_id,b.creator) h on a.id=h.job_id and h.creator=c1.account ");
        sql2.append(" left join ( select aa.creator,aa.job_id,count(distinct aa.cv_id) cvContactCount from ( ");		
		sql2.append("       select  a.ref_cvid cv_id,a.creator,b.job_id from search_project_cc_list a,contract_job_search_project b where a.project_id=b.project_id and   a.create_dt>=? and a.create_dt<? ");
		sql2.append("       union all ");
		sql2.append("       select  a.cv_id,a.creator,b.job_id from search_project_cv_crm a ,contract_job_search_project b where a.project_id=b.project_id and   a.create_dt>=? and a.create_dt<?  ");		
		sql2.append("     ) aa group by aa.creator,aa.job_id ) t3 on t3.job_id=a.id and t3.creator=c1.account      ");
		//内部推荐数
		sql2.append(" left join  (select job_id,creator,count(*) recomCount from contract_job_cv_recom where create_dt>=? and create_dt<?  group by job_id,creator) r1 on a.id=r1.job_id and r1.creator=c1.account ");
        sql2.append(" left join  (select  a.job_id,b.creator,count(distinct b.cv_id) giCount from contract_job_search_project a,search_project_interview b where a.project_id=b.project_id and   b.create_dt>=? and b.create_dt<?  group by a.job_id,b.creator) e on a.id=e.job_id and e.creator=c1.account ");
        sql2.append(" left join (select b.job_id,a.creator,count(*) kiCount from  contract_job_cv_interview a ,contract_job_cv_recom b where b.id=a.recom_id  and   a.create_dt>=? and a.create_dt<? group by b.job_id,a.creator) k on k.job_id=a.id and k.creator=c1.account ");
        //sql2.append(" left join  (select job_id,count(*) cooperCount from contract_job_partner group by job_id) i on a.id=i.job_id ");
        //sql2.append(" left join  (select job_id,group_concat((cc.name) separator ',') jobManager from contract_job_partner aa,system_user bb,system_branch_dept cc where aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and bb.dept_id=cc.id  group by job_id) j on a.id=j.job_id ");
        //sql2.append(" left join  (select job_id,c.creator,sum(ifnull(c.estimate_ar,0)) assistAmount from contract_job_cv_offer c,contract_job_cv_recom d where    d.id=c.recom_id and   c.create_dt>=? and c.create_dt<?  group by job_id,c.creator) i on a.id=i.job_id and  i.creator=c1.account ");
        sql2.append(" left join  ( ");
        sql2.append(" select a.id,e.account,sum(ifnull(f.final_ar*e.percent/100,0)) assistAmount from  contract_job a ");        
       sql2.append("   inner join contract_job_cv_recom c on a.id=c.job_id  ");
       sql2.append("   inner join contract_job_cv_offer d on d.recom_id=c.id  ");
       sql2.append("   inner join contract_job_offer_partner e on d.id=e.offer_id and e.role='BA' ");
       sql2.append("   inner join account_pn f on d.id=f.offer_id  ");
       sql2.append("   where  f.send_dt>=? and f.send_dt<? ");
       sql2.append("   group by e.account,a.id  ");
       sql2.append(" ) i on a.id=i.id and  i.account=c1.account  ");
        sql2.append(" left join (select id,account,dept_id,status,title_id,name from system_user) m on m.account=c1.account ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) j on j.id=m.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) j on j.user_id=m.id ");
        //sql2.append(" left join (select id,name from system_branch_title) l on l.id=m.title_id ");
        sql2.append(" left join (select b2.user_id,group_concat((b3.name) separator ',') name from system_user_title b2,system_branch_title b3 where  b2.title_id=b3.id  group by b2.user_id) l on l.user_id=m.id ");
        //system_branch_dept
        sql2.append(" where 1=1 ");
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        if(isNotEmpty(query.getJobStatus())) {
	        sql2.append(" and  a.status = ? ");
	        values.add(query.getJobStatus());	      
        }
        sql2.append(" ) t where 1=1 ");
        
        
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        
        
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getJobName())) {
	        sql2.append(" and  t.jobName like ? ");
	        values.add("%"+query.getJobName()+"%");
	        
        }
        if(isNotEmpty(query.getCustName())) {
	        sql2.append(" and  t.custName like ? ");
	        values.add("%"+query.getCustName()+"%");
	        
        }
        if(isNotEmpty(query.getJobStatus())) {
	        sql2.append(" and  t.jobStatus = ? ");
	        values.add(query.getJobStatus());
	        
        }
        
        if(isNotEmpty(query.getPartner())) {
        	sql2.append(" and  exists(select 1 from contract_job_partner aa where t.id=aa.job_id and aa.account=? ) ");
	        values.add(query.getPartner());
	                
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authBAReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }else sql2.append(" order by t.account ");
        
        
        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
        if (l!=null&&l.size()>0)
            return (Map)l.get(0);
        else return new HashMap();
	}
	
	

	@Override
	//业绩监控
	public List yjmonitor(ReportQuery query) {
		
		// TODO Auto-generated method stub
		//业绩监控															
		//姓名	部门	状态	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL

		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select *,if(t.ptype='A','OFFER',if(t.ptype='B','PN',if(t.ptype='C','到款',''))) ctype from ( ");        
        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
        
        
              
        String curYear=query.getYear();
        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
        int inextYear=StringHelper.parseInt(curYear, 0)+1;
        String nextYear=String.valueOf(inextYear);
 //      contract_job_cv_offer.apply_d 收到offer日期       
//        account_pn.send_dt 发出PN时间
//        account_pn.final_ar offer收款金额
        //(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0)) total
        //--Offer --
        sql2.append(" select a.id userId,a.account,'A' ptype,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.jan,0),2) jan,round(ifnull(b.feb,0),2) feb,round(ifnull(b.mar,0),2) mar,round(ifnull(b.apr,0),2) apr,round(ifnull(b.may,0),2) may,round(ifnull(b.jun,0),2) jun,round(ifnull(b.jul,0),2) jul,round(ifnull(b.aug,0),2) aug,round(ifnull(b.sep,0),2) sep,round(ifnull(b.oct,0),2) oct,round(ifnull(b.nov,0),2) nov,round(ifnull(b.dec1,0),2) dec1,round(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0),2) total  from system_user a "); 
        sql2.append(" left join ( ");
        sql2.append(" select e.account, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-01-01',if(c.apply_d<'").append(curYear).append("-02-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) jan, "); // 
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-02-01',if(c.apply_d<'").append(curYear).append("-03-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) feb, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-03-01',if(c.apply_d<'").append(curYear).append("-04-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) mar, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-04-01',if(c.apply_d<'").append(curYear).append("-05-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) apr, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-05-01',if(c.apply_d<'").append(curYear).append("-06-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) may, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-06-01',if(c.apply_d<'").append(curYear).append("-07-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) jun, ");               
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-07-01',if(c.apply_d<'").append(curYear).append("-08-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) jul,  ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-08-01',if(c.apply_d<'").append(curYear).append("-09-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) aug, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-09-01',if(c.apply_d<'").append(curYear).append("-10-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) sep, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-10-01',if(c.apply_d<'").append(curYear).append("-11-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) oct, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-11-01',if(c.apply_d<'").append(curYear).append("-12-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) nov, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-12-01',if(c.apply_d<'").append(inextYear).append("-01-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) dec1 ");

                	                
        //sql2.append("  from  account_pn a ");        
        //sql2.append(" inner join contract_job_cv_offer c on c.id=a.offer_id ");
        sql2.append("  from  contract_job_cv_offer c ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0))/100 rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");        
        sql2.append(" where  not exists(select 1 from account_pn f where c.id=f.offer_id and f.send_dt is not null )  ");
        sql2.append(" group by  e.account ");
        sql2.append(" ) b on b.account=a.account  ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        
        
        sql2.append(" union all ");        
        //--PN --
        sql2.append(" select a.id userId,a.account,'B' ptype,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.jan,0),2) jan,round(ifnull(b.feb,0),2) feb,round(ifnull(b.mar,0),2) mar,round(ifnull(b.apr,0),2) apr,round(ifnull(b.may,0),2) may,round(ifnull(b.jun,0),2) jun,round(ifnull(b.jul,0),2) jul,round(ifnull(b.aug,0),2) aug,round(ifnull(b.sep,0),2) sep,round(ifnull(b.oct,0),2) oct,round(ifnull(b.nov,0),2) nov,round(ifnull(b.dec1,0),2) dec1,round(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0),2) total  from system_user a "); 
        sql2.append(" left join ( ");
        sql2.append(" select e.account, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-01-01',if(f.send_dt<'").append(curYear).append("-02-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) jan, "); // 
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-02-01',if(f.send_dt<'").append(curYear).append("-03-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) feb, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-03-01',if(f.send_dt<'").append(curYear).append("-04-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) mar, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-04-01',if(f.send_dt<'").append(curYear).append("-05-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) apr, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-05-01',if(f.send_dt<'").append(curYear).append("-06-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) may, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-06-01',if(f.send_dt<'").append(curYear).append("-07-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) jun, ");               
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-07-01',if(f.send_dt<'").append(curYear).append("-08-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) jul,  ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-08-01',if(f.send_dt<'").append(curYear).append("-09-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) aug, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-09-01',if(f.send_dt<'").append(curYear).append("-10-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) sep, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-10-01',if(f.send_dt<'").append(curYear).append("-11-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) oct, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-11-01',if(f.send_dt<'").append(curYear).append("-12-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) nov, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-12-01',if(f.send_dt<'").append(inextYear).append("-01-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) dec1 ");
                	                
        //sql2.append("  from  account_pn a ");        
        //sql2.append(" inner join contract_job_cv_offer c on c.id=a.offer_id ");
        sql2.append("  from  contract_job_cv_offer c ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0))/100 rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
        sql2.append("   inner join account_pn f on c.id=f.offer_id  ");
        
        sql2.append(" group by  e.account ");
        sql2.append(" ) b on b.account=a.account  ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        
        
        sql2.append(" union all ");
        
        //--到款 --
        sql2.append(" select a.id userId,a.account,'C' ptype,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.jan,0),2) jan,round(ifnull(b.feb,0),2) feb,round(ifnull(b.mar,0),2) mar,round(ifnull(b.apr,0),2) apr,round(ifnull(b.may,0),2) may,round(ifnull(b.jun,0),2) jun,round(ifnull(b.jul,0),2) jul,round(ifnull(b.aug,0),2) aug,round(ifnull(b.sep,0),2) sep,round(ifnull(b.oct,0),2) oct,round(ifnull(b.nov,0),2) nov,round(ifnull(b.dec1,0),2) dec1,round(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0),2) total  from system_user a "); 
        sql2.append(" left join ( ");
        sql2.append(" select e.account, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-01-01',if(a.ar_dt<'").append(curYear).append("-02-01',ifnull(a.amount,0)*e.percent/100,0),0)) jan, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-02-01',if(a.ar_dt<'").append(curYear).append("-03-01',ifnull(a.amount,0)*e.percent/100,0),0)) feb, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-03-01',if(a.ar_dt<'").append(curYear).append("-04-01',ifnull(a.amount,0)*e.percent/100,0),0)) mar, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-04-01',if(a.ar_dt<'").append(curYear).append("-05-01',ifnull(a.amount,0)*e.percent/100,0),0)) apr, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-05-01',if(a.ar_dt<'").append(curYear).append("-06-01',ifnull(a.amount,0)*e.percent/100,0),0)) may, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-06-01',if(a.ar_dt<'").append(curYear).append("-07-01',ifnull(a.amount,0)*e.percent/100,0),0)) jun, ");               
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-07-01',if(a.ar_dt<'").append(curYear).append("-08-01',ifnull(a.amount,0)*e.percent/100,0),0)) jul,  ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append	("-08-01',if(a.ar_dt<'").append(curYear).append("-09-01',ifnull(a.amount,0)*e.percent/100,0),0)) aug, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-09-01',if(a.ar_dt<'").append(curYear).append("-10-01',ifnull(a.amount,0)*e.percent/100,0),0)) sep, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-10-01',if(a.ar_dt<'").append(curYear).append("-11-01',ifnull(a.amount,0)*e.percent/100,0),0)) oct, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-11-01',if(a.ar_dt<'").append(curYear).append("-12-01',ifnull(a.amount,0)*e.percent/100,0),0)) nov, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-12-01',if(a.ar_dt<'").append(inextYear).append("-01-01',ifnull(a.amount,0)*e.percent/100,0),0)) dec1 ");
                	                
        sql2.append("  from  account_ar a ");
        sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
        sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0))/100 rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
        sql2.append(" group by  e.account ");
        sql2.append(" ) b on b.account=a.account  ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        
        
        
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
       
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        if(isNotEmpty(query.getYjType())) {
	        sql2.append(" and  t.ptype = ? ");
	        values.add(query.getYjType());
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        	sql2.append(" ,t.ptype ");
        }else
        sql2.append(" order by  t.account,t.ptype ");
        
        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
		//return null;
	}
	public List yjmonitorTotal(ReportQuery query) {
		// TODO Auto-generated method stub
		//业绩监控															
		//姓名	部门	状态	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	TOTAL

		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select if(t.ptype='A','OFFER',if(t.ptype='B','PN',if(t.ptype='C','到款',''))) ctype,sum(t.jan) jan,sum(t.feb) feb,sum(t.mar) mar,sum(t.apr) apr,sum(t.may) may,sum(t.jun) jun,sum(t.jul) jul,sum(t.aug) aug,sum(t.sep) sep,sum(t.oct) oct,sum(t.nov) nov,sum(t.dec1) dec1,sum(t.total) total  from ( ");        
        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
        
        sql2.append(" select * from ( ");
        
        String curYear=query.getYear();
        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
        int inextYear=StringHelper.parseInt(curYear, 0)+1;
        String nextYear=String.valueOf(inextYear);
        //(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0)) total
        //sql2.append(" select a.account,'A' ptype,a.name,a.dept_id deptId,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(b.jan,2) jan,round(b.feb,2) feb,round(b.mar,2) mar,round(b.apr,2) apr,round(b.may,2) may,round(b.jun,2) jun,round(b.jul,2) jul,round(b.aug,2) aug,round(b.sep,2) sep,round(b.oct,2) oct,round(b.nov,2) nov,round(b.dec1,2) dec1,round(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0),2) total  from system_user a ");
        
        sql2.append(" select a.id userId,a.account,'A' ptype,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.jan,0),2) jan,round(ifnull(b.feb,0),2) feb,round(ifnull(b.mar,0),2) mar,round(ifnull(b.apr,0),2) apr,round(ifnull(b.may,0),2) may,round(ifnull(b.jun,0),2) jun,round(ifnull(b.jul,0),2) jul,round(ifnull(b.aug,0),2) aug,round(ifnull(b.sep,0),2) sep,round(ifnull(b.oct,0),2) oct,round(ifnull(b.nov,0),2) nov,round(ifnull(b.dec1,0),2) dec1,round(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0),2) total  from system_user a "); 
        sql2.append(" left join ( ");
        sql2.append(" select e.account, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-01-01',if(c.apply_d<'").append(curYear).append("-02-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) jan, "); // 
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-02-01',if(c.apply_d<'").append(curYear).append("-03-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) feb, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-03-01',if(c.apply_d<'").append(curYear).append("-04-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) mar, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-04-01',if(c.apply_d<'").append(curYear).append("-05-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) apr, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-05-01',if(c.apply_d<'").append(curYear).append("-06-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) may, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-06-01',if(c.apply_d<'").append(curYear).append("-07-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) jun, ");               
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-07-01',if(c.apply_d<'").append(curYear).append("-08-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) jul,  ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-08-01',if(c.apply_d<'").append(curYear).append("-09-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) aug, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-09-01',if(c.apply_d<'").append(curYear).append("-10-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) sep, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-10-01',if(c.apply_d<'").append(curYear).append("-11-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) oct, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-11-01',if(c.apply_d<'").append(curYear).append("-12-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) nov, ");
        sql2.append(" sum(if(c.apply_d>='").append(curYear).append("-12-01',if(c.apply_d<'").append(inextYear).append("-01-01',ifnull(c.estimate_ar,0)*e.percent/100,0),0)) dec1 ");

                	                
        //sql2.append("  from  account_pn a ");        
        //sql2.append(" inner join contract_job_cv_offer c on c.id=a.offer_id ");
        sql2.append("  from  contract_job_cv_offer c ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0))/100 rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");        
        sql2.append(" where  not exists(select 1 from account_pn f where c.id=f.offer_id and f.send_dt is not null)  ");
        sql2.append(" group by  e.account ");
        sql2.append(" ) b on b.account=a.account  ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        
        
        sql2.append(" union all ");
        
        sql2.append(" select a.id userId,a.account,'B' ptype,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.jan,0),2) jan,round(ifnull(b.feb,0),2) feb,round(ifnull(b.mar,0),2) mar,round(ifnull(b.apr,0),2) apr,round(ifnull(b.may,0),2) may,round(ifnull(b.jun,0),2) jun,round(ifnull(b.jul,0),2) jul,round(ifnull(b.aug,0),2) aug,round(ifnull(b.sep,0),2) sep,round(ifnull(b.oct,0),2) oct,round(ifnull(b.nov,0),2) nov,round(ifnull(b.dec1,0),2) dec1,round(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0),2) total  from system_user a ");
        sql2.append(" left join ( ");
        sql2.append(" select e.account, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-01-01',if(f.send_dt<'").append(curYear).append("-02-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) jan, "); // 
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-02-01',if(f.send_dt<'").append(curYear).append("-03-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) feb, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-03-01',if(f.send_dt<'").append(curYear).append("-04-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) mar, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-04-01',if(f.send_dt<'").append(curYear).append("-05-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) apr, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-05-01',if(f.send_dt<'").append(curYear).append("-06-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) may, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-06-01',if(f.send_dt<'").append(curYear).append("-07-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) jun, ");               
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-07-01',if(f.send_dt<'").append(curYear).append("-08-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) jul,  ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-08-01',if(f.send_dt<'").append(curYear).append("-09-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) aug, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-09-01',if(f.send_dt<'").append(curYear).append("-10-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) sep, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-10-01',if(f.send_dt<'").append(curYear).append("-11-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) oct, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-11-01',if(f.send_dt<'").append(curYear).append("-12-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) nov, ");
        sql2.append(" sum(if(f.send_dt>='").append(curYear).append("-12-01',if(f.send_dt<'").append(inextYear).append("-01-01',ifnull(f.final_ar,0)*e.percent/100,0),0)) dec1 ");
                	                
        //sql2.append("  from  account_pn a ");        
        //sql2.append(" inner join contract_job_cv_offer c on c.id=a.offer_id ");
        sql2.append("  from  contract_job_cv_offer c ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0))/100 rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
        //sql2.append("   inner join contract_job_offer_partner e on d.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
        sql2.append("   inner join account_pn f on c.id=f.offer_id  ");
        sql2.append(" group by  e.account ");
        sql2.append(" ) b on b.account=a.account  ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        
        
        sql2.append(" union all ");
        
        //
        sql2.append(" select a.id userId,a.account,'C' ptype,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.jan,0),2) jan,round(ifnull(b.feb,0),2) feb,round(ifnull(b.mar,0),2) mar,round(ifnull(b.apr,0),2) apr,round(ifnull(b.may,0),2) may,round(ifnull(b.jun,0),2) jun,round(ifnull(b.jul,0),2) jul,round(ifnull(b.aug,0),2) aug,round(ifnull(b.sep,0),2) sep,round(ifnull(b.oct,0),2) oct,round(ifnull(b.nov,0),2) nov,round(ifnull(b.dec1,0),2) dec1,round(ifnull(jan,0)+ifnull(feb,0)+ifnull(mar,0)+ifnull(apr,0)+ifnull(may,0)+ifnull(jun,0)+ifnull(jul,0)+ifnull(aug,0)+ifnull(sep,0)+ifnull(oct,0)+ifnull(nov,0)+ifnull(dec1,0),2) total  from system_user a ");
        
        sql2.append(" left join ( ");
        sql2.append(" select e.account, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-01-01',if(a.ar_dt<'").append(curYear).append("-02-01',ifnull(a.amount,0)*e.percent/100,0),0)) jan, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-02-01',if(a.ar_dt<'").append(curYear).append("-03-01',ifnull(a.amount,0)*e.percent/100,0),0)) feb, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-03-01',if(a.ar_dt<'").append(curYear).append("-04-01',ifnull(a.amount,0)*e.percent/100,0),0)) mar, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-04-01',if(a.ar_dt<'").append(curYear).append("-05-01',ifnull(a.amount,0)*e.percent/100,0),0)) apr, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-05-01',if(a.ar_dt<'").append(curYear).append("-06-01',ifnull(a.amount,0)*e.percent/100,0),0)) may, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-06-01',if(a.ar_dt<'").append(curYear).append("-07-01',ifnull(a.amount,0)*e.percent/100,0),0)) jun, ");               
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-07-01',if(a.ar_dt<'").append(curYear).append("-08-01',ifnull(a.amount,0)*e.percent/100,0),0)) jul,  ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-08-01',if(a.ar_dt<'").append(curYear).append("-09-01',ifnull(a.amount,0)*e.percent/100,0),0)) aug, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-09-01',if(a.ar_dt<'").append(curYear).append("-10-01',ifnull(a.amount,0)*e.percent/100,0),0)) sep, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-10-01',if(a.ar_dt<'").append(curYear).append("-11-01',ifnull(a.amount,0)*e.percent/100,0),0)) oct, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-11-01',if(a.ar_dt<'").append(curYear).append("-12-01',ifnull(a.amount,0)*e.percent/100,0),0)) nov, ");
        sql2.append(" sum(if(a.ar_dt>='").append(curYear).append("-12-01',if(a.ar_dt<'").append(inextYear).append("-01-01',ifnull(a.amount,0)*e.percent/100,0),0)) dec1 ");
                	                
        sql2.append("  from  account_ar a ");
        sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
        sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" inner join (select a.id,d.account,sum(ifnull(d.percent,0))/100 rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
        //sql2.append("   inner join contract_job_offer_partner e on d.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
        sql2.append(" group by  e.account ");
        sql2.append(" ) b on b.account=a.account  ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        
        
        
        sql2.append(" ) t0 where 1=1 ");
        List values = new ArrayList();
       
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t0.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t0.status = ? ");
	        values.add(query.getAccountStatus());
        }
        if(isNotEmpty(query.getYjType())) {
	        sql2.append(" and  t0.ptype = ? ");
	        values.add(query.getYjType());
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t0.parent_ids like ? ");
        		else sb.append(" t0.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t0.account"));
        sql2.append(" ) t group by if(t.ptype='A','OFFER',if(t.ptype='B','PN',if(t.ptype='C','到款','')))  ");
        //FileHelper.write("f:\\aaa1.sql", sql2.toString());
        
        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
        //if (l!=null&&l.size()>0)
          //  return (Map)l.get(0);
        //else return new HashMap();
		return l;
	}

	@Override
	//其他工作指标
	public List othertarget(ReportQuery query) {
		// TODO Auto-generated method stub
		//其他工作指标									
		//姓名	部门	人选上班	保证期人选数	保证期跟进数	人选毁约	保证期离职	延期回款	退款金额	投诉

		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select * from ( ");
        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
        sql2.append(" select a.id,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,ifnull(b.dutyCount,0) dutyCount,ifnull(c.bzqCount,0) bzqCount,ifnull(d.gjCount,0) gjCount,ifnull(e.hyCount,0) hyCount,ifnull(f.lzCount,0) lzCount,ifnull(h.aveDelayAmount,0) aveDelayAmount,null tkAmount,ifnull(j.tsCount,0) tsCount  from system_user a ");
        //人选上班
        sql2.append(" left join (select c.account,count(*) dutyCount from  contract_job_cv_offer a ,contract_job_cv_recom b,contract_job_offer_partner c where a.id=c.offer_id and c.role<>'BA' and  b.id=a.recom_id and a.status=1 and   a.checkin_d>=? and a.checkin_d<? group by c.account) b on b.account=a.account ");
        //保证期人选数(非离职人员)
        sql2.append(" left join (select c.account,count(distinct b.id)  bzqCount from  contract_job_cv_offer a ,contract_job_cv_recom b ,contract_job_offer_partner c where a.id=c.offer_id and c.role<>'BA' and b.id=a.recom_id and a.status=1  and    ((a.onduty_d>=? and a.onduty_d<?)  or (DATE_ADD(a.onduty_d, INTERVAL a.trial MONTH)>=? and DATE_ADD(a.onduty_d, INTERVAL a.trial MONTH)<?) ) group by c.account) c on  c.account=a.account ");
        //保证期跟进数(非离职)
        sql2.append(" left join (select d.account,count(distinct b.id) gjCount from  contract_job_cv_offer a ,contract_job_cv_recom b , contract_job_cv_crm c ,contract_job_offer_partner d where a.id=d.offer_id and d.role<>'BA' and b.id=c.recom_id and b.id=a.recom_id and a.status=1   and    ((a.onduty_d>=? and a.onduty_d<?)  or (DATE_ADD(a.onduty_d, INTERVAL a.trial MONTH)>=? and DATE_ADD(a.onduty_d, INTERVAL a.trial MONTH)<?) ) group by d.account) d on d.account=a.account ");
        //人选毁约
        sql2.append(" left join (select d.account,count(distinct b.id) hyCount from  contract_job_cv_offer a ,contract_job_cv_recom b , contract_job_cv_crm c ,contract_job_offer_partner d where a.id=d.offer_id and d.role<>'BA' and b.id=c.recom_id   and b.id=a.recom_id and c.crm_status=3  and    ((a.onduty_d>=? and a.onduty_d<?)  or (DATE_ADD(a.onduty_d, INTERVAL a.trial MONTH)>=? and DATE_ADD(a.onduty_d, INTERVAL a.trial MONTH)<?) ) group by d.account) e on e.account=a.account ");
        //保证期离职status=3
        sql2.append(" left join (select c.account,count(distinct b.id)  lzCount from  contract_job_cv_offer a ,contract_job_cv_recom b ,contract_job_offer_partner c where a.id=c.offer_id and c.role<>'BA' and b.id=a.recom_id and a.status=3  and    ((a.onduty_d>=? and a.onduty_d<?)  or (DATE_ADD(a.onduty_d, INTERVAL a.trial MONTH)>=? and DATE_ADD(a.onduty_d, INTERVAL a.trial MONTH)<?) ) group by c.account) f on  f.account=a.account ");
        //延期回款
        //sql2.append(" left join  (select c.creator,sum(ifnull(b.final_ar,0)) delayAmount from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d where   c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and a.confirm_dt>a.ar_dt and   a.confirm_dt>=? and a.confirm_dt<?  group by c.creator) h on   h.creator=a.account ");
        //sql2.append(" left join  (  select aa.account,sum(DATEDIFF(aa.confirmDt,aa.arDt))/count(aa.id) aveDelayAmount from ( select f.account ,a.id,min(a.ar_dt) arDt,min(a.confirm_dt) confirmDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_offer_partner f where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and c.id=f.offer_id and  f.role<>'BA' and e.create_dt>=? and e.create_dt<? group by a.id,f.account   ) aa group by  aa.account ) h on h.account=a.account");
        sql2.append(" left join  (  select aa.account,sum(DATEDIFF(aa.arDt,aa.expectDt))/count(aa.id) aveDelayAmount from ( select f.account ,a.id,min(a.ar_dt) arDt,min(a.expect_dt) expectDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_offer_partner f where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and c.id=f.offer_id and  f.role<>'BA' and e.create_dt>=? and e.create_dt<? group by a.id,f.account   ) aa group by  aa.account ) h on h.account=a.account");
        //退款金额
        
        //投诉
        sql2.append(" left join  (select a.be_complainer,count(*) tsCount from customer_complain a where a.complain_dt>=? and a.complain_dt<? group by a.be_complainer) j on   j.be_complainer=a.account ");
        
        
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
      //保证期人选数(非离职人员)
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
      //保证期跟进数(非离职)
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        
        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());   
	}

	//@Override
	//个人效率分析
	public List staffEffAnalyse(ReportQuery query) {
		// TODO Auto-generated method stub
		//个人效率分析										
		//姓名	部门	分公司	累计创建职位	累计成功职位	成功率	累计业绩	累计回款	平均金额	平均成功职位周期	平均DSO
		StringBuffer sql2 = new StringBuffer();		 
        //sql2.append("select *,ifnull(round(t.dso/t.succJobCount,2),0) dso from ( ");
		sql2.append("select * from ( ");
        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
        sql2.append(" select a.id,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,j.name branchCompanyName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,ifnull(b.jobCount,0) jobCount,ifnull(c.succJobCount,0) succJobCount,concat(ifnull(round(c.succJobCount/b.jobCount*100,2),0),'%') succrate,ifnull(d.yjAmount,0) yjAmount,ifnull(e.hkAmount,0) hkAmount,ifnull(round(d.aveAmount,2),0) aveAmount,ifnull(f.aveUseDate,0) aveUseDate,ifnull(h.dso,0) dso  from system_user a ");
		//sql2.append(" select a.account,a.name,a.dept_id deptId,i.name deptName,h.name branchCompanyName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,ifnull(b.jobCount,0) jobCount,ifnull(c.succJobCount,0) succJobCount,concat(ifnull(round(c.succJobCount/b.jobCount*100,2),0),'%') succrate,ifnull(d.yjAmount,0) yjAmount,ifnull(e.hkAmount,0) hkAmount,ifnull(round(d.yjAmount/c.succJobCount,2),0) aveAmount,ifnull(f.aveUseDate,0) aveUseDate  from system_user a ");
        //累计创建职位
        sql2.append(" left join (select b.account,count(*) jobCount from  contract_job a,contract_job_partner b where a.id=b.job_id and (b.role='MC') and a.open_d>=? and a.open_d<? group by b.account) b on b.account=a.account ");
        //累计成功职位
        sql2.append(" left join (select b.account,count(distinct a.id) succJobCount from  contract_job a,contract_job_cv_recom c,contract_job_partner b,contract_job_cv_offer d where a.id=b.job_id and a.id=c.job_id and c.id=d.recom_id and d.checkin_d is not null and (b.role='MC') and  a.open_d>=? and a.open_d<?   group by b.account) c on c.account=a.account ");
        //成功率
        
        //累计业绩(预计收费)
        sql2.append(" left join ( ");
        sql2.append(" select e.account,sum(ifnull(f.final_ar*e.percent/100,0)) yjAmount,sum(ifnull(f.final_ar*e.percent/100,0))/count(distinct a.id) aveAmount  from  contract_job a ");
        
        sql2.append("   inner join contract_job_cv_recom c on a.id=c.job_id  ");
        sql2.append("   inner join contract_job_cv_offer d on d.recom_id=c.id  ");
        //sql2.append("   inner join contract_job_offer_partner e on d.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on d.id=e.offer_id  ");
        sql2.append("   inner join account_pn f on d.id=f.offer_id  ");
       // sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=c.job_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job_cv_offer a,contract_job_offer_partner b where a.id=b.offer_id group by a.id,b.account) e on e.id=d.id ");
        sql2.append("   where  f.send_dt>=? and f.send_dt<? ");
        sql2.append("   group by e.account  ");
        sql2.append(" ) d on d.account=a.account  ");
        //累计回款
        sql2.append(" left join ( ");
        sql2.append(" select e.account,sum(ifnull(a.amount*e.percent/100,0)) hkAmount ");                       	               
        sql2.append("  from  account_ar a ");
        sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
        sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job_cv_offer a,contract_job_offer_partner b where a.id=b.offer_id group by a.id,b.account) e on e.id=c.id ");
        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
        sql2.append(" inner join contract_job f on f.id=d.job_id ");
       
        sql2.append("   where a.ar_dt is not null and   f.create_dt>=? and f.create_dt<? ");
        sql2.append(" group by  e.account ");
        sql2.append(" ) e on e.account=a.account  ");
        //平均金额
        
        //平均成功职位周期
        //sql2.append(" left join (select aa.account,sum(aa.useDate)/count(*) aveUseDate from (  select b.account ,d.id,DATEDIFF(min(d.apply_d),min(a.open_d)) useDate from  contract_job a, contract_job_partner b,contract_job_cv_recom c,contract_job_cv_offer d where a.id=c.job_id and c.id=d.recom_id and  b.job_id=a.id and d.checkin_d is not null and b.role='MC' and  a.open_d>=? and a.open_d<?   group by d.id,b.account) aa group by aa.account    ) f on f.account=a.account ");
        sql2.append(" left join (select aa.account,sum(aa.useDate)/count(distinct aa.jobId) aveUseDate from (  select b.account ,d.id,a.id jobId,DATEDIFF(min(d.apply_d),min(a.open_d)) useDate from  contract_job a, contract_job_partner b,contract_job_cv_recom c,contract_job_cv_offer d where a.id=c.job_id and c.id=d.recom_id and  b.job_id=a.id and d.checkin_d is not null and b.role='MC' and  a.open_d>=? and a.open_d<?   group by d.id,b.account) aa group by aa.account    ) f on f.account=a.account ");
        //DSO合计(可用来算平均DSO)
        //sql2.append(" left join  (  select aa.creator,sum(DATEDIFF(aa.confirmDt,aa.sendDt)) sumdso from ( select f.account creator,a.id,min(b.send_dt) sendDt,min(a.confirm_dt) confirmDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and e.id=f.job_id and  f.role<>'BA' and e.create_dt>=? and e.create_dt<?   group by a.id,f.account  ) aa group by  aa.creator  ) h on h.creator=a.account");
        //sql2.append(" left join  (  select aa.creator,sum(DATEDIFF(aa.arDt,aa.expectDt)) sumdso from ( select f.account creator,a.id,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and e.id=f.job_id and  f.role<>'BA' and e.create_dt>=? and e.create_dt<?   group by a.id,f.account  ) aa group by  aa.creator  ) h on h.creator=a.account");
        //sql2.append(" left join  (  select aa.creator,sum(DATEDIFF(aa.arDt,aa.expectDt)) dso from ( select f.account creator,a.id,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and e.id=f.job_id and  f.role<>'BA' and e.create_dt>=? and e.create_dt<?   group by a.id,f.account  ) aa group by  aa.creator  ) h on h.creator=a.account");
        sql2.append(" left join  (  select aa.account,round(sum(if(aa.dso=-99999,0,aa.dso))/sum(if(aa.dso=-99999,0,1)),2) dso from ( select f.account,a.id,ifnull(DATEDIFF(min(a.ar_dt),min(c.checkin_d)),-99999) dso,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where a.pn_nbr=b.nbr and e.id=f.job_id and  f.role<>'BA'  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and  e.create_dt>=? and e.create_dt<?   group by a.id,f.account  ) aa group by  aa.account  ) h on  h.account=a.account");
        
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        //sql2.append(" left join system_branch_company h on h.id=a.branch_company_id ");
         sql2.append(" left join (select aa.user_id,group_concat((b5.name) separator ',') name from (select distinct b2.user_id,b4.branch_company_id from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id ) aa,system_branch_company b5 where aa.branch_company_id=b5.id  group by aa.user_id) j on j.user_id=a.id ");
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
       
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        
        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());  
	}
	
	public Map staffEffAnalyseSum(ReportQuery query) {
		// TODO Auto-generated method stub
		//个人效率分析										
		//姓名	部门	分公司	累计创建职位	累计成功职位	成功率	累计业绩	累计回款	平均金额	平均成功职位周期	平均DSO
		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select sum(t.jobCount)/count(distinct t.account),sum(t.succJobCount)/count(distinct t.account),concat(ifnull(round(sum(t.succJobCount)/sum(t.jobCount)*100,2),0),'%') succrate,ifnull(round(sum(t.sumdso)/sum(t.countdso),2),0) dso,sum(t.yjAmount) yjAmount,ifnull(round(sum(t.aveAmount)/count(distinct t.account),2),0) aveAmount,ifnull(round(sum(t.aveUseDate)/count(distinct t.account),2),0) aveUseDate from ( ");
		//sql2.append("select sum(t.jobCount)/count(distinct t.account),sum(t.succJobCount)/count(distinct t.account) from ( ");
        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
        sql2.append(" select a.id,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,j.name branchCompanyName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,ifnull(b.jobCount,0) jobCount,ifnull(c.succJobCount,0) succJobCount,ifnull(round(c.succJobCount/b.jobCount,2),0) succrate,ifnull(d.yjAmount,0) yjAmount,ifnull(e.hkAmount,0) hkAmount,ifnull(round(d.aveAmount,2),0) aveAmount,ifnull(f.aveUseDate,0) aveUseDate,h.sumdso,h.countdso  from system_user a ");
        //累计创建职位
        sql2.append(" left join (select b.account,count(*) jobCount from  contract_job a,contract_job_partner b where a.id=b.job_id and (b.role='MC') and a.open_d>=? and a.open_d<? group by b.account) b on b.account=a.account ");
        //累计成功职位
        sql2.append(" left join (select b.account,count(distinct a.id) succJobCount from  contract_job a,contract_job_cv_recom c,contract_job_partner b,contract_job_cv_offer d where a.id=b.job_id and a.id=c.job_id and d.checkin_d is not null and c.id=d.recom_id and (b.role='MC') and  a.open_d>=? and a.open_d<?   group by b.account) c on c.account=a.account ");
        //成功率
        
        //累计业绩(预计收费)
        sql2.append(" left join ( ");
        sql2.append(" select e.account,sum(ifnull(f.final_ar*e.percent/100,0)) yjAmount,sum(ifnull(f.final_ar*e.percent/100,0))/count(distinct a.id) aveAmount from  contract_job a ");
        
        sql2.append("   inner join contract_job_cv_recom c on a.id=c.job_id  ");
        sql2.append("   inner join contract_job_cv_offer d on d.recom_id=c.id  ");
        //sql2.append("   inner join contract_job_offer_partner e on d.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on d.id=e.offer_id  ");
        sql2.append("   inner join account_pn f on d.id=f.offer_id  ");
       // sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=c.job_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job_cv_offer a,contract_job_offer_partner b where a.id=b.offer_id group by a.id,b.account) e on e.id=d.id ");
        sql2.append("   where  f.send_dt>=? and f.send_dt<? ");
        sql2.append("   group by e.account  ");
        sql2.append(" ) d on d.account=a.account  ");
        //累计回款
        sql2.append(" left join ( ");
        sql2.append(" select e.account,sum(ifnull(a.amount*e.percent/100,0)) hkAmount ");                       	               
        sql2.append("  from  account_ar a ");
        sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
        sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job_cv_offer a,contract_job_offer_partner b where a.id=b.offer_id group by a.id,b.account) e on e.id=c.id ");
        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
        sql2.append(" inner join contract_job f on f.id=d.job_id ");
       
        sql2.append("   where a.ar_dt is not null and f.create_dt>=? and f.create_dt<? ");
        sql2.append(" group by  e.account ");
        sql2.append(" ) e on e.account=a.account  ");
        //平均金额
        
        //平均成功职位周期
        sql2.append(" left join (select aa.account,sum(aa.useDate)/count(distinct aa.jobId) aveUseDate from (  select b.account ,d.id,a.id jobId,DATEDIFF(min(d.apply_d),min(a.open_d)) useDate from  contract_job a, contract_job_partner b,contract_job_cv_recom c,contract_job_cv_offer d where a.id=c.job_id and c.id=d.recom_id and  b.job_id=a.id and d.checkin_d is not null and b.role='MC' and  a.open_d>=? and a.open_d<?   group by d.id,b.account) aa group by aa.account    ) f on f.account=a.account ");
        //DSO合计(可用来算平均DSO)
        //sql2.append(" left join  (  select aa.creator,sum(DATEDIFF(aa.arDt,aa.expectDt)) sumdso from ( select f.account creator,a.id,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and e.id=f.job_id and  f.role<>'BA' and e.create_dt>=? and e.create_dt<?   group by a.id,f.account  ) aa group by  aa.creator  ) h on h.creator=a.account");
        sql2.append(" left join  (  select aa.account,round(sum(if(aa.dso=-99999,0,aa.dso))/sum(if(aa.dso=-99999,0,1)),2) dso,sum(if(aa.dso=-99999,0,aa.dso)) sumdso,sum(if(aa.dso=-99999,0,1)) countdso from ( select f.account,a.id,ifnull(DATEDIFF(min(a.ar_dt),min(c.checkin_d)),-99999) dso,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where a.pn_nbr=b.nbr and e.id=f.job_id and  f.role<>'BA'  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and  e.create_dt>=? and e.create_dt<?   group by a.id,f.account  ) aa group by  aa.account  ) h on  h.account=a.account");
        
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        //sql2.append(" left join system_branch_company h on h.id=a.branch_company_id ");
        sql2.append(" left join (select aa.user_id,group_concat((b5.name) separator ',') name from (select distinct b2.user_id,b4.branch_company_id from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id ) aa,system_branch_company b5 where aa.branch_company_id=b5.id  group by aa.user_id) j on j.user_id=a.id ");
        
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
       
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        
        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
        if (l!=null&&l.size()>0)
            return (Map)l.get(0);
        else return new HashMap();  
	}

	@Override
	//客户效益分析
	public Page customerEffAnalyse(ReportQuery query) {
		// TODO Auto-generated method stub		
		//客户效益分析									
		//客户简称	客户经理	累计服务费	合作顾问	累计合作职位数	累计成功职位数	成功率	平均金额	平均职位周期	平均DSO
	
		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select * from ( ");
        //sql2.append(" select a.id,c.name custName,d.jobManager,ifnull(e.ljServiceAmount,0) ljServiceAmount,d.jobCoo,ifnull(f.jobCount,0) jobCount,ifnull(h.succJobCount,0) succJobCount,ifnull(i.aveUseDate,0) aveUseDate,j.sumdso,ifnull(round((t.succJobCount/t.jobCount)*100,2),0) succrate,ifnull(round((t.ljServiceAmount/t.jobCount)*100,2),0) aveJobAmount,ifnull(round(t.sumdso/t.succJobCount,2),0) dso  from customer a ");
        sql2.append(" select a.id,c.abbr_name custName,c.name cnName,c.full_name enName,d.jobManager,ifnull(e.ljServiceAmount,0) ljServiceAmount,d.jobCoo,ifnull(f.jobCount,0) jobCount,ifnull(h.succJobCount,0) succJobCount,ifnull(round(i.aveUseDate,2),0) aveUseDate,ifnull(round((ifnull(h.succJobCount,0)/ifnull(f.jobCount,0))*100,2),0) succrate,ifnull(round((ifnull(e.ljServiceAmount,0)/ifnull(h.succJobCount,0)),2),0) aveJobAmount,ifnull(j.dso,0) dso  from customer a ");
        sql2.append(" inner join company c on a.company_id=c.id ");
        //客户经理  //合作顾问
        //sql2.append(" select a.cust_id,group_concat((if(b.role='MC',c.name,null) ) separator ',') as jobManager,group_concat((if(b.role='PC',c.name,null) ) separator ',') as jobCoo from contract_job a,contract_job_partner b,system_user c where a.id=b.job_id and b.account=c.account group by a.cust_id ");
        //sql2.append(" left join ( select bb.cust_id,group_concat((if(bb.role='MC',bb.name,null) ) separator ',') as jobManager,group_concat((if(bb.role='PC',bb.name,null) ) separator ',') as jobCoo from (  select distinct a.cust_id,b.role,b.account,c.name from contract_job a,contract_job_partner b,system_user c where a.id=b.job_id and b.account=c.account and a.create_dt>=? and a.create_dt<? ) bb group by bb.cust_id ) d on  d.cust_id=a.id ");
        sql2.append(" left join ( select bb.cust_id,group_concat((if(bb.role='BDM',bb.account,null) ) separator ',') as jobManager,group_concat((if(bb.role='BDP',bb.account,null) ) separator ',') as jobCoo from  ( select a.cust_id,b.role,b.account from customer_contract a,customer_contract_staff b where a.id=b.contract_id union all select b.cust_id,c.role,c.account from customer_contract a,customer_contract_service_company b,customer_contract_staff c where a.id=b.contract_id and a.id=c.contract_id) bb group by bb.cust_id ) d on  d.cust_id=a.id ");
        //累计服务费
        sql2.append(" left join ( ");
        sql2.append(" select f.cust_id,sum(ifnull(b.final_ar,0)) ljServiceAmount ");                       	               
        //sql2.append("  from  account_ar a ");
        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
        sql2.append("  from  account_pn b ");
        sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");

        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job_cv_offer a,contract_job_offer_partner b where a.id=b.offer_id group by a.id,b.account) e on e.id=d.id ");        
        sql2.append(" inner join contract_job f on f.id=d.job_id ");
        //sql2.append("   where a.ar_dt is not null and   b.send_dt>=? and b.send_dt<? ");
        sql2.append("   where   b.send_dt>=? and b.send_dt<? ");
        sql2.append(" group by  f.cust_id ");
        sql2.append(" ) e on e.cust_id=a.id  ");               
        //累计合作职位数
        sql2.append(" left join (  select a.cust_id,count(*) jobCount  from contract_job a group by a.cust_id ) f on  f.cust_id=a.id  ");
        //累计成功职位数
        sql2.append(" left join (select a.cust_id,count(distinct a.id) succJobCount from  contract_job a,contract_job_cv_recom b,contract_job_cv_offer c where a.id=b.job_id and  b.id=c.recom_id    and  a.create_dt>=? and a.create_dt<?   group by a.cust_id) h on h.cust_id=a.id  ");
        //成功率
        //平均金额
        
        //平均职位周期
        sql2.append(" left join (select aa.cust_id,sum(aa.useDate)/count(distinct aa.id) aveUseDate from (  select a.cust_id,d.id,DATEDIFF(d.apply_d,a.open_d) useDate from  contract_job a, contract_job_cv_recom c,contract_job_cv_offer d where a.id=c.job_id and c.id=d.recom_id  and  d.apply_d>=? and d.apply_d<?   group by d.id,a.cust_id) aa group by aa.cust_id    ) i on i.cust_id=a.id   ");
        //平均DSO
        //DSO合计(可用来算平均DSO)
        sql2.append(" left join  (  select aa.cust_id,round(sum(if(aa.dso=-99999,0,aa.dso))/sum(if(aa.dso=-99999,0,1)),2) dso from ( select e.cust_id,a.id,sum(ifnull(DATEDIFF(a.ar_dt,c.checkin_d),-99999)) dso,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and  e.create_dt>=? and e.create_dt<?   group by a.id,e.cust_id  ) aa group by  aa.cust_id  ) j on  j.cust_id=a.id");
        
        
        //包含BD客户
        //sql2.append(" where  exists(select 1 from customer_contract a1 where a.id=a1.cust_id)  ");
        //sql2.append(" or  exists(select 1 from customer_contract_service_company a2 where a.id=a2.cust_id)  ");        
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        //values.add(query.getBeginD());
        //values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        if(isNotEmpty(query.getJobManager())) {
	        sql2.append(" and  t.jobManager like ? ");
	        values.add("%"+query.getJobManager()+"%");
	        
        }
        if(isNotEmpty(query.getJobCoo())) {
	        sql2.append(" and  t.jobCoo like ? ");
	        values.add("%"+query.getJobCoo()+"%");
	        
        }
        if(isNotEmpty(query.getCustName())) {
	        sql2.append(" and  (t.custName like ? or t.chName like ? or t.enName like ? )");
	        values.add("%"+query.getCustName()+"%");
	        values.add("%"+query.getCustName()+"%");
	        values.add("%"+query.getCustName()+"%");
	        
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportCustomerSQL(query.getOnlineUser().getUsername(),"t.id", "t.creator"));
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }        
        
        return super.getJdbcDao().createPage(sql2.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());

	}

	public Map customerEffAnalyseSum(ReportQuery query) {
		// TODO Auto-generated method stub		
		//客户效益分析									
		//客户简称	客户经理	累计服务费	合作顾问	累计合作职位数	累计成功职位数	成功率	平均金额	平均职位周期	平均DSO

		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select ifnull(round((sum(t.succJobCount)/sum(t.jobCount))*100,2),0) succrate,ifnull(round((sum(t.ljServiceAmount)/sum(t.succJobCount)),2),0) aveJobAmount,ifnull(sum(t.useDate)/sum(t.jobCount),0) aveUseDate,ifnull(round(sum(t.sumdso)/sum(t.countdso),2),0) dso from ( ");
        sql2.append(" select a.id,c.abbr_name custName,c.name cnName,c.full_name enName,d.jobManager,ifnull(e.ljServiceAmount,0) ljServiceAmount,d.jobCoo,ifnull(f.jobCount,0) jobCount,ifnull(h.succJobCount,0) succJobCount,ifnull(i.useDate,0) useDate,ifnull(round(i.aveUseDate,2),0) aveUseDate,j.dso,j.sumdso,j.countdso  from customer a ");        
        sql2.append(" inner join company c on a.company_id=c.id ");
        //客户经理  //合作顾问
        //sql2.append(" select a.cust_id,group_concat((if(b.role='MC',c.name,null) ) separator ',') as jobManager,group_concat((if(b.role='PC',c.name,null) ) separator ',') as jobCoo from contract_job a,contract_job_partner b,system_user c where a.id=b.job_id and b.account=c.account group by a.cust_id ");
        sql2.append(" left join ( select bb.cust_id,group_concat((if(bb.role='BDM',bb.account,null) ) separator ',') as jobManager,group_concat((if(bb.role='BDP',bb.account,null) ) separator ',') as jobCoo from  ( select a.cust_id,b.role,b.account from customer_contract a,customer_contract_staff b where a.id=b.contract_id union all select b.cust_id,c.role,c.account from customer_contract a,customer_contract_service_company b,customer_contract_staff c where a.id=b.contract_id and a.id=c.contract_id) bb group by bb.cust_id ) d on  d.cust_id=a.id ");
        //累计服务费
        sql2.append(" left join ( ");
        sql2.append(" select f.cust_id,ifnull(b.final_ar,0) ljServiceAmount ");                       	               
        //sql2.append("  from  account_ar a ");
        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
        sql2.append("  from  account_pn b ");
        sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        //sql2.append(" inner join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job_cv_offer a,contract_job_offer_partner b where a.id=b.offer_id group by a.id,b.account) e on e.id=c.id ");        
        sql2.append(" inner join contract_job f on f.id=d.job_id ");
        //sql2.append("   where a.ar_dt is not null and   b.send_dt>=? and b.send_dt<? ");
        sql2.append("   where   b.send_dt>=? and b.send_dt<? ");
        sql2.append(" group by  f.cust_id ");
        sql2.append(" ) e on e.cust_id=a.id  ");               
        //累计合作职位数
        sql2.append(" left join (  select a.cust_id,count(*) jobCount  from contract_job a group by a.cust_id ) f on  f.cust_id=a.id  ");
        //累计成功职位数
        //sql2.append(" left join (select a.cust_id,count(distinct a.id) succJobCount from  contract_job a,contract_job_cv_recom b,contract_job_cv_offer c,account_pn d,account_ar e where a.id=b.job_id and  b.id=c.recom_id and c.id=d.offer_id and e.pn_nbr=d.nbr  and  a.create_dt>=? and a.create_dt<?   group by a.cust_id) h on h.cust_id=a.id  ");
        sql2.append(" left join (select a.cust_id,count(distinct a.id) succJobCount from  contract_job a,contract_job_cv_recom b,contract_job_cv_offer c where a.id=b.job_id and  b.id=c.recom_id    and  a.create_dt>=? and a.create_dt<?   group by a.cust_id) h on h.cust_id=a.id  ");
        //成功率
        //平均金额
        
        //平均职位周期
        sql2.append(" left join (select aa.cust_id,sum(aa.useDate)/count(distinct aa.id) aveUseDate,sum(aa.useDate) useDate from (  select a.cust_id,d.id,DATEDIFF(d.apply_d,ifnull(a.open_d,a.create_dt)) useDate from  contract_job a, contract_job_cv_recom c,contract_job_cv_offer d where a.id=c.job_id and c.id=d.recom_id and    d.apply_d>=? and d.apply_d<?   group by d.id,a.cust_id) aa group by aa.cust_id    ) i on i.cust_id=a.id   ");
        //平均DSO
        //DSO合计(可用来算平均DSO)
        //sql2.append(" left join  (  select aa.cust_id,sum(DATEDIFF(aa.confirmDt,aa.sendDt)) sumdso from ( select e.cust_id,e.id,min(b.send_dt) sendDt,min(a.confirm_dt) confirmDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and  e.create_dt>=? and e.create_dt<?   group by e.cust_id  ) aa group by  aa.cust_id  ) j on  j.cust_id=a.id");
        //sql2.append(" left join  (  select aa.cust_id,sum(DATEDIFF(aa.arDt,aa.sendDt)) sumdso from ( select e.cust_id,a.id,min(b.send_dt) sendDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and  e.create_dt>=? and e.create_dt<?   group by a.id,e.cust_id  ) aa group by  aa.cust_id  ) j on  j.cust_id=a.id");
        //sql2.append(" left join  (  select aa.cust_id,sum(ifnull(DATEDIFF(aa.arDt,aa.expectDt),0)) sumdso from ( select e.cust_id,a.id,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and  e.create_dt>=? and e.create_dt<?   group by a.id,e.cust_id  ) aa group by  aa.cust_id  ) j on  j.cust_id=a.id");
        //sql2.append(" left join  (  select aa.cust_id,sum(aa.dso) sumdso from ( select e.cust_id,a.id,sum(ifnull(DATEDIFF(a.ar_dt,c.checkin_d),0)) dso,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and  e.create_dt>=? and e.create_dt<?   group by a.id,e.cust_id  ) aa group by  aa.cust_id  ) j on  j.cust_id=a.id");
        sql2.append(" left join  (  select aa.cust_id,round(sum(if(aa.dso=-99999,0,aa.dso))/sum(if(aa.dso=-99999,0,1)),2) dso,sum(if(aa.dso=-99999,0,aa.dso)) sumdso,sum(if(aa.dso=-99999,0,1)) countdso from ( select e.cust_id,a.id,sum(ifnull(DATEDIFF(a.ar_dt,c.checkin_d),-99999)) dso,min(a.expect_dt) expectDt,min(a.ar_dt) arDt from account_ar a, account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where a.pn_nbr=b.nbr  and c.id=b.offer_id and d.id=c.recom_id and d.job_id=e.id and  e.create_dt>=? and e.create_dt<?   group by a.id,e.cust_id  ) aa group by  aa.cust_id  ) j on  j.cust_id=a.id");
        
        
        //包含BD客户
        //sql2.append(" where  exists(select 1 from customer_contract a1 where a.id=a1.cust_id)  ");
        //sql2.append(" or  exists(select 1 from customer_contract_service_company a2 where a.id=a2.cust_id)  ");
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        //values.add(query.getBeginD());
        //values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        if(isNotEmpty(query.getJobManager())) {
	        sql2.append(" and  t.jobManager like ? ");
	        values.add("%"+query.getJobManager()+"%");
	        
        }
        if(isNotEmpty(query.getJobCoo())) {
	        sql2.append(" and  t.jobCoo like ? ");
	        values.add("%"+query.getJobCoo()+"%");
	        
        }
        if(isNotEmpty(query.getCustName())) {
            sql2.append(" and  (t.custName like ? or t.chName like ? or t.enName like ? ) ");
	        values.add("%"+query.getCustName()+"%");
	        values.add("%"+query.getCustName()+"%");
	        values.add("%"+query.getCustName()+"%");
	        
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportCustomerSQL(query.getOnlineUser().getUsername(),"t.id", "t.creator"));
//        if(isNotEmpty(query.getSort())) {
//        	sql2.append(" order by t.").append(query.getSort());
//        	
//        	if(isNotEmpty(query.getDir())) {
//        		sql2.append(" ").append(query.getDir());            	
//        	}
//        }
        
        
        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
        if (l!=null&&l.size()>0)
            return (Map)l.get(0);
        else return new HashMap();  

	}
	@Override
	//资料整理报告
	public Page staffDataReport(ReportQuery query) {
		// TODO Auto-generated method stub
		//资料整理报告
		//员工姓名（允许多选查询）	部门（允许多选查询）	资料类型（人选/公司）允许多选	动作（新增/修改）允许多选	简历/公司完善度（空白，1,2,3……申请删除，SC要求删除）允许多选	数量

		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select * from ( ");
              
                
        sql2.append(" select a.id,a.account,1 datatype,1 optype,'人选新增' type,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,b.integrity,count(b.id) cc  from system_user a "); 
        sql2.append(" left join cv b on b.creator=a.account and b.create_dt>=? and b.create_dt<? ");       
        sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" group by  a.id,a.account,b.integrity");
        sql2.append(" union all ");
        sql2.append(" select a.id,a.account,1 datatype,2 optype,'人选修改' type,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,b.integrity,count(b.id) cc  from system_user a "); 
        sql2.append(" left join cv b on b.modifier=a.account and b.modified_dt>=? and b.modified_dt<? ");       
        sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" group by  a.account,b.integrity");
        sql2.append(" union all ");
        sql2.append(" select a.id,a.account,2 datatype,1 optype,'公司新增' type,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,b.integrity,count(b.id) cc  from system_user a "); 
        sql2.append(" left join company b on b.creator=a.account and b.create_dt>=? and b.create_dt<? ");       
        sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" group by  a.account,b.integrity");
        sql2.append(" union all ");
        sql2.append(" select a.id,a.account,2 datatype,2 optype,'公司修改' type,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,b.integrity,count(b.id) cc  from system_user a "); 
        sql2.append(" left join company b on b.modifier=a.account and b.modified_dt>=? and b.modified_dt<? ");                            
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        sql2.append(" group by  a.id,a.account,b.integrity");
        
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
       
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if(isNotEmpty(query.getDatatype())) {
	        sql2.append(" and  t.datatype in ");
	        sql2.append("("+query.getDatatype()+")");
        }
        if(isNotEmpty(query.getOptype())) {
	        sql2.append(" and  t.optype in ");
	        sql2.append("("+query.getOptype()+")");
        }
        if(isNotEmpty(query.getIntegrity())) {
	        sql2.append(" and  t.integrity in ");
	        sql2.append("("+query.getIntegrity()+")");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        sql2.append(" order by  t.account,t.type ");
        
        
		//List i=super.getJdbcDao().findByQuery(queryPlus.toString(), values.toArray(), new MapObjectRowMapper());
		
        //int i=super.getJdbcDao().getJdbcTemplate().queryForInt(queryPlus.toString(), values.toArray());
        //System.out.println("test count ok:"+i+"--"+System.currentTimeMillis());
        //return new Page();
        //return null;
        return super.getJdbcDao().createPage(sql2.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());

	}

	public List staffDataReportlist(ReportQuery query) {
		// TODO Auto-generated method stub
		//资料整理报告
		//员工姓名（允许多选查询）	部门（允许多选查询）	资料类型（人选/公司）允许多选	动作（新增/修改）允许多选	简历/公司完善度（空白，1,2,3……申请删除，SC要求删除）允许多选	数量

		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select * from ( ");
              
                
        sql2.append(" select a.id,a.account,1 datatype,1 optype,'人选新增' type,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,b.integrity,count(b.id) cc  from system_user a "); 
        sql2.append(" left join cv b on b.creator=a.account and b.create_dt>=? and b.create_dt<? ");       
        //sql2.append(" left join (select id,name,parent_ids  from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        sql2.append(" group by  a.id,a.account,b.integrity");
        sql2.append(" union all ");
        sql2.append(" select a.id,a.account,1 datatype,2 optype,'人选修改' type,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,b.integrity,count(b.id) cc  from system_user a "); 
        sql2.append(" left join cv b on b.modifier=a.account and b.modified_dt>=? and b.modified_dt<? ");       
        //sql2.append(" left join (select id,name,parent_ids  from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        sql2.append(" group by  a.id,a.account,b.integrity");
        sql2.append(" union all ");
        sql2.append(" select a.id,a.account,2 datatype,1 optype,'公司新增' type,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,b.integrity,count(b.id) cc  from system_user a "); 
        sql2.append(" left join company b on b.creator=a.account and b.create_dt>=? and b.create_dt<? ");       
        //sql2.append(" left join (select id,name,parent_ids  from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        sql2.append(" group by  a.id,a.account,b.integrity");
        sql2.append(" union all ");
        sql2.append(" select a.id,a.account,2 datatype,2 optype,'公司修改' type,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,b.integrity,count(b.id) cc  from system_user a "); 
        sql2.append(" left join company b on b.modifier=a.account and b.modified_dt>=? and b.modified_dt<? ");                            
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        sql2.append(" group by  a.id,a.account,b.integrity");
                
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
       
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if(isNotEmpty(query.getDatatype())) {
	        sql2.append(" and  t.datatype in ");
	        sql2.append("("+query.getDatatype()+")");
        }
        if(isNotEmpty(query.getOptype())) {
	        sql2.append(" and  t.optype in ");
	        sql2.append("("+query.getOptype()+")");
        }
        if(isNotEmpty(query.getIntegrity())) {
	        sql2.append(" and  t.integrity in ");
	        sql2.append("("+query.getIntegrity()+")");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        sql2.append(" order by  t.account,t.type ");
        
        
		//List i=super.getJdbcDao().findByQuery(queryPlus.toString(), values.toArray(), new MapObjectRowMapper());
		
        //int i=super.getJdbcDao().getJdbcTemplate().queryForInt(queryPlus.toString(), values.toArray());
        //System.out.println("test count ok:"+i+"--"+System.currentTimeMillis());
        //return new Page();
        //return null;
        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());

	}

	@Override
	public List jobefflist(ReportQuery query) {
		// TODO Auto-generated method stub
		StringBuffer sql2 = new StringBuffer();		 
        sql2.append("select *,concat(floor(if(recomCount>0,ifnull(offerCount,0)/recomCount,0)*10000/100),'%') offerrate from ( ");
        sql2.append(" select a.id,c.abbr_name custName,c.name chName,c.full_name enName,a.name jobName,a.status jobStatus,j.deptName,a.creator,a.job_manager jobManager,a.create_dt createDt,DATEDIFF(now(),ifnull(a.open_d,a.create_dt)) jobdays,d.ondutyD,e.jdCount,f.recomCount,h.offerCount,h.estimateAr,i.cooperCount  from contract_job a ");
        sql2.append(" inner join customer b on a.cust_id=b.id ");
        sql2.append(" inner join company c on b.company_id=c.id ");
        sql2.append(" left join (select b.job_id,min(a.checkin_d) ondutyD from  contract_job_cv_offer a ,contract_job_cv_recom b where b.id=a.recom_id and a.checkin_d is not null group by b.job_id) d on d.job_id=a.id ");
        sql2.append(" left join  (select job_id,count(distinct cv_id) jdCount from contract_job_cv_jd where type="+ContractJobCvJd.TYPE_JD+" group by job_id) e on a.id=e.job_id ");
        sql2.append(" left join  (select job_id,count(*) recomCount from contract_job_cv_recom group by job_id) f on a.id=f.job_id ");
        //sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(t1.estimate_ar) estimateAr from contract_job_cv_offer t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) h on a.id=h.job_id ");
        sql2.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(t3.final_ar) estimateAr from contract_job_cv_offer t1,contract_job_cv_recom t2,account_pn t3 where t1.recom_id=t2.id and t1.id=t3.offer_id  group by t2.job_id) h on a.id=h.job_id ");
        sql2.append(" left join  (select job_id,count(*) cooperCount from contract_job_partner group by job_id) i on a.id=i.job_id ");
        sql2.append(" left join  (select job_id,group_concat((cc.name) separator ',') deptName from contract_job_partner aa,system_user bb,system_branch_dept cc where aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and bb.dept_id=cc.id  group by job_id) j on a.id=j.job_id ");
        //system_branch_dept
        sql2.append(" ) t where 1=1 ");
        List values = new ArrayList();
        
        if(isNotEmpty(query.getAccount())) {
	        sql2.append(" and  t.account = ? ");
	        values.add(query.getAccount());
	        System.out.println("account:"+query.getAccount());
        }
        if(isNotEmpty(query.getJobName())) {
	        sql2.append(" and  t.jobName like ? ");
	        values.add("%"+query.getJobName()+"%");
	        
        }
        if(isNotEmpty(query.getCustName())) {
	        //sql2.append(" and  t.custName like ? ");
            sql2.append(" and  (t.custName like ? or t.chName like ? or t.enName like ? )");
	        values.add("%"+query.getCustName()+"%");
	        values.add("%"+query.getCustName()+"%");
	        values.add("%"+query.getCustName()+"%");
	        
        }
        if(isNotEmpty(query.getJobStatus())) {
	        sql2.append(" and  t.jobStatus = ? ");
	        values.add(query.getJobStatus());
	        
        }
        
        if(isNotEmpty(query.getPartner())) {
        	sql2.append(" and  exists(select 1 from contract_job_partner aa where t.id=aa.job_id and aa.account=? ) ");
	        values.add(query.getPartner());
	                
        }
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		//if(sb.length()>0) sb.append(" or ").append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc where bb.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		//else sb.append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc where bb.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		if(sb.length()>0) sb.append(" or ").append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc,system_user_title dd where dd.user_id=bb.id and dd.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		else sb.append("  exists(select 1 from contract_job_partner aa,system_user bb,system_branch_dept cc,system_user_title dd where dd.user_id=bb.id and dd.dept_id=cc.id and aa.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and aa.account=bb.account and  t.id=aa.job_id and cc.parent_ids like ? ) ");
        		values.add("%/"+s[i]+"/%");	
        	}
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(),"t.id", "t.creator"));
        
        if(isNotEmpty(query.getSort())) {
        	sql2.append(" order by t.").append(query.getSort());
        	
        	if(isNotEmpty(query.getDir())) {
        		sql2.append(" ").append(query.getDir());            	
        	}
        }
        
        
        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
	}

	@Override
	public List bothyj(ReportQuery query) {
		// TODO Auto-generated method stub
		//个人业绩总结

		 StringBuffer sql2 = new StringBuffer();		 
	        sql2.append("select *,if(t.ptype='A','OFFER',if(t.ptype='B','业绩','')) ctype from ( ");
	        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
	        sql2.append(" select 'A' ptype,concat(a.id,b.offer_id,'A') id,b.id jobId,b.jobName,b.cvName,b.offer_id offerId,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,date_format(b.applyD,'%Y-%m-%d') applyD,date_format(b.sendDt,'%Y-%m-%d') sendDt,date_format(h.confirmDt,'%Y-%m-%d') confirmDt  from system_user a ");
	        
	        //Q1
	        //account_ar
	        //account_pn.final_ar offer收款金额
	      //contract_job_cv_offer.estimate_ar offer预计收费
	        String curYear=query.getYear();
	        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
	        int inextYear=StringHelper.parseInt(curYear, 0)+1;
	        String nextYear=String.valueOf(inextYear);
	        List values = new ArrayList();
	        sql2.append(" left join ( ");
	        sql2.append(" select e.account,h.id,c.id offer_id,h.name jobName,i.cv_name cvName,k.abbr_name custName, ");
	        sql2.append(" sum(ifnull(c.estimate_ar,0)) yjamount,sum(ifnull(c.estimate_ar,0)*ifnull(e.percent,0)/100) yjrate,max(c.apply_d) applyD,min(b.send_dt) sendDt ");
	        
	        	        
	        
	        //sql2.append("  from  account_ar a ");
	        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
	        //sql2.append("  from  account_pn b ");	        
	        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
	        sql2.append("  from  contract_job_cv_offer c ");
	        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
	        sql2.append(" left join account_pn b on  c.id=b.offer_id ");
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where b.account=? and a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	       // values.add(query.getAccount());
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	        //sql2.append(" left join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
	        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
	        sql2.append(" inner join contract_job h on h.id=d.job_id ");
	        sql2.append(" inner join cv i on i.id=d.cv_id ");
	        sql2.append(" inner join customer j on j.id=h.cust_id ");
	        sql2.append(" inner join company k on k.id=j.company_id ");
	        
	        //sql2.append(" where  b.send_dt>=? and b.send_dt<? ");	        
	        sql2.append(" where  c.apply_d>=? and c.apply_d<? ");
	        
	      //已经有“发账单日期”的offer就不在这边表中统计
	        sql2.append(" and not exists(select 1 from account_pn h1 where h1.offer_id=c.id and h1.send_dt is not null ) ");
	        
	        sql2.append(" group by  e.account,h.id,c.id ");
	        sql2.append(" ) b on b.account=a.account    ");
	        //sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' and   e.create_dt>=? and e.create_dt<?  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("'  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");

	        
	        /////////////////
	        sql2.append(" union all ");
	        
	        //sql2.append(" select 'B' ctype ,concat(a.id,b.offer_id,'B') id,b.id jobId,b.jobName,b.cvName,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,date_format(b.applyD,'%Y-%m-%d') applyD,date_format(b.sendDt,'%Y-%m-%d') sendDt,date_format(h.confirmDt,'%Y-%m-%d') confirmDt  from system_user a ");
	        sql2.append(" select 'B' ptype,concat(a.id,b.offer_id,'B') id,b.id jobId,b.jobName,b.cvName,b.offer_id offerId,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,date_format(b.applyD,'%Y-%m-%d') applyD,date_format(b.sendDt,'%Y-%m-%d') sendDt,date_format(h.confirmDt,'%Y-%m-%d') confirmDt  from system_user a ");
	        //Q1
	        //account_ar
	        //String curYear=query.getYear();
	        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
	        //int inextYear=StringHelper.parseInt(curYear, 0)+1;
	        //String nextYear=String.valueOf(inextYear);
	        //List values = new ArrayList();
	        sql2.append(" left join ( ");
	        sql2.append(" select e.account,h.id,c.id offer_id,h.name jobName,i.cv_name cvName,k.abbr_name custName, ");
	        sql2.append(" sum(ifnull(b.final_ar,0)) yjamount,sum(ifnull(b.final_ar,0)*ifnull(e.percent,0)/100) yjrate,max(c.apply_d) applyD,min(b.send_dt) sendDt ");
	        
	        	        
	        
	        //sql2.append("  from  account_ar a ");
	        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
	        //sql2.append("  from  account_pn b ");	        
	        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
	        sql2.append("  from  contract_job_cv_offer c ");
	        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
	        sql2.append(" left join account_pn b on  c.id=b.offer_id ");
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where b.account=? and a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	       // values.add(query.getAccount());
	        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
	        //sql2.append(" left join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
	        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
	        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
	        sql2.append(" inner join contract_job h on h.id=d.job_id ");
	        sql2.append(" inner join cv i on i.id=d.cv_id ");
	        sql2.append(" inner join customer j on j.id=h.cust_id ");
	        sql2.append(" inner join company k on k.id=j.company_id ");
	        
	        sql2.append(" where  b.send_dt>=? and b.send_dt<? ");
	        //sql2.append(" where  c.apply_d>=? and c.apply_d<? ");
	        sql2.append(" group by  e.account,h.id,c.id ");
	        sql2.append(" ) b on b.account=a.account    ");
	        //sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' and   e.create_dt>=? and e.create_dt<?  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_offer_partner f where c.id=f.offer_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("'  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
	        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
	        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
	        
	        
	        
	        sql2.append(" ) t where 1=1 and t.yj>0 ");
	       
	       // values.add(query.getBeginD());
	        //values.add(query.getEndD());
	        
	        
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        values.add(query.getBeginD());
	        values.add(query.getEndD());
	        //values.add(new Date());
	        //values.add(new Date());
	        
		    //sql2.append(" and  exists (select 1 from contract_job_partner bb where bb.job_id=t.jobId and bb.account=?  ) ");
		    //values.add(query.getAccount());
	        if(isNotEmpty(query.getAccount())) {		        
	        	String[] s=query.getAccount().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
	        		else sb.append("'"+s[i]+"'");	        		
	        	}		
	        	
	        	sql2.append(" and  t.account in ("+sb.toString()+")");
	        }
	        if(isNotEmpty(query.getYjmType())) {
	        	 sql2.append(" and  t.ptype = ? ");
	 	        values.add(query.getYjmType());
	        }
		    
	        if(isNotEmpty(query.getAccountStatus())) {
		        sql2.append(" and  t.status = ? ");
		        values.add(query.getAccountStatus());
	        }
	        if(isNotEmpty(query.getDeptId())) {
	        	sql2.append(" and  ( ");
	        	String[] s=query.getDeptId().split(",");
	        	StringBuffer sb=new StringBuffer();
	        	for(int i=0;i<s.length;i++){
	        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
	        		else sb.append(" t.parent_ids like ? ");
	        		values.add("%/"+s[i]+"/%");	
	        	}		
	        	sql2.append(sb.toString());
		        sql2.append(" ) ");
	        }
	        
	        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
	        //排重
	        sql2.append(" group by t.id  ");
	        if(isNotEmpty(query.getSort())) {
	        	sql2.append(" order by t.").append(query.getSort());
	        	
	        	if(isNotEmpty(query.getDir())) {
	        		sql2.append(" ").append(query.getDir());            	
	        	}
	        } else
	            sql2.append(" order by  t.account,t.ptype ");
	        
	        //logger.info("业绩监控SQL:"+sql2.toString());
	        return super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());  
	        //logger.info("业绩监控SQL:"+sql2.toString());

	}

	@Override
	public List bothyjTotal(ReportQuery query) {
		StringBuffer sql2 = new StringBuffer();
		sql2.append("select *,if(tt.ptype='A','OFFER',if(tt.ptype='B','业绩','')) ctype from ( ");
        sql2.append("select 'c' account,'A' ptype,count(distinct t.jobId) jobId,sum(ifnull(t.yjrate,0)) yjrate from ( ");
        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
        //sql2.append(" select count(distinct b.id) jobId,sum(yjrate) yjrate  from system_user a ");
        sql2.append(" select a.id,b.id jobId,b.jobName,b.cvName,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,b.applyD,b.sendDt,h.confirmDt  from system_user a ");
        //Q1
        //account_ar
        String curYear=query.getYear();
        if (StringHelper.isEmpty(curYear)) curYear=String.valueOf(DateHelper.getNowYear());
        int inextYear=StringHelper.parseInt(curYear, 0)+1;
        String nextYear=String.valueOf(inextYear);
        List values = new ArrayList();
        sql2.append(" left join ( ");
        sql2.append(" select e.account,h.id,c.id offer_id,i.id cv_id,h.name jobName,i.cv_name cvName,k.abbr_name custName, ");
        sql2.append(" sum(ifnull(c.estimate_ar,0)) yjamount,sum(ifnull(c.estimate_ar,0)*ifnull(e.percent,0)/100) yjrate,max(c.apply_d) applyD,min(b.send_dt) sendDt ");
        
        	        
        
        //sql2.append("  from  account_ar a ");
        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
        //sql2.append("  from  account_pn b ");
        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
        sql2.append("  from  contract_job_cv_offer c ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        sql2.append(" left join account_pn b on  c.id=b.offer_id ");
        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where b.account=? and a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //values.add(query.getAccount());
        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" left join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
        sql2.append(" inner join contract_job h on h.id=d.job_id ");
        sql2.append(" inner join cv i on i.id=d.cv_id ");
        sql2.append(" inner join customer j on j.id=h.cust_id ");
        sql2.append(" inner join company k on k.id=j.company_id ");
        
       // sql2.append(" where  b.send_dt>=? and b.send_dt<? ");
        sql2.append(" where  c.apply_d>=? and c.apply_d<? ");
      //已经有“发账单日期”的offer就不在这边表中统计
        sql2.append(" and not exists(select 1 from account_pn h1 where h1.offer_id=c.id and h1.send_dt is not null ) ");

        sql2.append(" group by  e.account,h.id,c.id  ");
        sql2.append(" ) b on b.account=a.account    ");
        //sql2.append(" left join  (select c.creator,d.job_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where   c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and   e.create_dt>=? and e.create_dt<?  group by c.creator,d.job_id) h on   h.job_id=b.id and h.creator=a.account ");
        sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' and   e.create_dt>=? and e.create_dt<?  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        
        
        sql2.append(" ) t where 1=1 and t.yj>0 ");
       
        
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        values.add(query.getBeginD());
        values.add(query.getEndD());
        //values.add(new Date());
        //values.add(new Date());
        
	    //sql2.append(" and  exists (select 1 from contract_job_partner bb where bb.job_id=t.jobId and bb.account=?  ) ");
	    //values.add(query.getAccount());
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));
        
        //---------合并
        sql2.append(" union all ");
        
        sql2.append("select 'c' account,'B' ptype,count(distinct t.jobId) jobId,sum(ifnull(t.yjrate,0)) yjrate from ( ");
        //sql2.append(" select a.account,a.name,ifnull(b.bdCallCount,0) bdCallCount,ifnull(b.bdCallCountFin,0) bdCallCountFin,ifnull(c.customerVisitCount,0) customerVisitCount,ifnull(c.customerVisitCountFin,0) customerVisitCountFin,ifnull(d.giCount,0) giCount,ifnull(d.giCountFin,0) giCountFin,ifnull(e.recomCount,0) recomCount,ifnull(f.recomCountFin,0) recomCountFin,0 cvCount,ifnull(h.cvCount,0) cvCountFin  from system_user a ");
        //sql2.append(" select count(distinct b.id) jobId,sum(yjrate) yjrate  from system_user a ");
        sql2.append(" select a.id,b.id jobId,b.jobName,b.cvName,b.custName,a.account,a.name,a.dept_id deptId,i.parent_ids,i.name deptName,if(a.status='N','正常',if(a.status='B','锁定','')) statusName,a.status,round(ifnull(b.yjamount,0),2) yj,round(ifnull(b.yjrate,0),2) yjrate,b.applyD,b.sendDt,h.confirmDt  from system_user a ");
        //Q1
        //account_ar
        
        sql2.append(" left join ( ");
        sql2.append(" select e.account,h.id,c.id offer_id,i.id cv_id,h.name jobName,i.cv_name cvName,k.abbr_name custName, ");
        sql2.append(" sum(ifnull(b.final_ar,0)) yjamount,sum(ifnull(b.final_ar,0)*ifnull(e.percent,0)/100) yjrate,max(c.apply_d) applyD,min(b.send_dt) sendDt ");
        
        	        
        
        //sql2.append("  from  account_ar a ");
        //sql2.append(" inner join account_pn b on a.pn_nbr=b.nbr ");
        //sql2.append("  from  account_pn b ");
        //sql2.append(" inner join contract_job_cv_offer c on c.id=b.offer_id ");
        sql2.append("  from  contract_job_cv_offer c ");
        sql2.append(" inner join contract_job_cv_recom d on d.id=c.recom_id ");
        sql2.append(" left join account_pn b on  c.id=b.offer_id ");
        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where b.account=? and a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //values.add(query.getAccount());
        //sql2.append(" left join (select a.id,b.account,sum(ifnull(b.percent,0)) rate from  contract_job a,contract_job_partner b where a.id=b.job_id group by a.id,b.account) e on e.id=d.job_id ");
        //sql2.append(" left join (select a.id,d.account,sum(ifnull(d.percent,0)) rate from contract_job a,contract_job_cv_recom b,contract_job_cv_offer c ,contract_job_offer_partner d where a.id=b.job_id and b.id=c.recom_id and c.id=d.offer_id group by a.id,d.account) e on e.id=d.job_id ");
        //sql2.append("   inner join contract_job_offer_partner e on c.id=e.offer_id and e.role<>'BA' ");
        sql2.append("   inner join (select offer_id,account,sum(percent) percent from  contract_job_offer_partner where role<>'BA' group by offer_id,account) e on c.id=e.offer_id  ");
        sql2.append(" inner join contract_job h on h.id=d.job_id ");
        sql2.append(" inner join cv i on i.id=d.cv_id ");
        sql2.append(" inner join customer j on j.id=h.cust_id ");
        sql2.append(" inner join company k on k.id=j.company_id ");
        
        sql2.append(" where  b.send_dt>=? and b.send_dt<? ");
        //sql2.append(" where  c.apply_d>=? and c.apply_d<? ");
        sql2.append(" group by  e.account,h.id,c.id  ");
        sql2.append(" ) b on b.account=a.account    ");
        //sql2.append(" left join  (select c.creator,d.job_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e where   c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and   e.create_dt>=? and e.create_dt<?  group by c.creator,d.job_id) h on   h.job_id=b.id and h.creator=a.account ");
        //sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_partner f where e.id=f.job_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' and   e.create_dt>=? and e.create_dt<?  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
        sql2.append(" left join  (select f.account,d.job_id,b.offer_id,min(a.confirm_dt) confirmDt from account_ar a,account_pn b,contract_job_cv_offer c,contract_job_cv_recom d,contract_job e,contract_job_offer_partner f where c.id=f.offer_id and  c.id=b.offer_id  and a.pn_nbr=b.nbr and d.id=c.recom_id and d.job_id=e.id  and  f.role<>'").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("'  group by f.account,d.job_id,b.offer_id) h on   h.job_id=b.id and b.offer_id=h.offer_id and h.account=a.account ");
        //sql2.append(" left join (select id,name,parent_ids from system_branch_dept) i on i.id=a.dept_id ");
        sql2.append(" left join (select b2.user_id,group_concat((Convert(b4.parent_ids,char)) separator ',') parent_ids,group_concat((b4.name) separator ',') name from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) i on i.user_id=a.id ");
        sql2.append(" ) t where 1=1 and t.yj>0 ");
       
        values.add(query.getBeginD());
        values.add(query.getEndD());
        
        //values.add(query.getBeginD());
        //values.add(query.getEndD());
        //values.add(new Date());
        //values.add(new Date());
        
	    //sql2.append(" and  exists (select 1 from contract_job_partner bb where bb.job_id=t.jobId and bb.account=?  ) ");
	    //values.add(query.getAccount());
        if(isNotEmpty(query.getAccount())) {		        
        	String[] s=query.getAccount().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(",").append("'"+s[i]+"'");
        		else sb.append("'"+s[i]+"'");	        		
        	}		
        	
        	sql2.append(" and  t.account in ("+sb.toString()+")");
        }
        if(isNotEmpty(query.getAccountStatus())) {
	        sql2.append(" and  t.status = ? ");
	        values.add(query.getAccountStatus());
        }
        
       
        
        
        if(isNotEmpty(query.getDeptId())) {
        	sql2.append(" and  ( ");
        	String[] s=query.getDeptId().split(",");
        	StringBuffer sb=new StringBuffer();
        	for(int i=0;i<s.length;i++){
        		if(sb.length()>0) sb.append(" or ").append(" t.parent_ids like ? ");
        		else sb.append(" t.parent_ids like ? ");
        		values.add("%/"+s[i]+"/%");	
        	}		
        	sql2.append(sb.toString());
	        sql2.append(" ) ");
        }
        
        if (query.getOnlineUser()!=null)sql2.append(AuthSqlUtil.authReportSQL(query.getOnlineUser().getUsername(), "t.account"));


        
        sql2.append(" ) tt where 1=1 ");
        if(isNotEmpty(query.getYjmType())) {
        	 sql2.append(" and  tt.ptype = ? ");
 	        values.add(query.getYjmType());
        }
        
//        if(isNotEmpty(query.getSort())) {
//        	sql2.append(" order by t.").append(query.getSort());
//        	
//        	if(isNotEmpty(query.getDir())) {
//        		sql2.append(" ").append(query.getDir());            	
//        	}
//        }
        List l=super.getJdbcDao().findByQuery(sql2.toString(),values.toArray(), new MapObjectRowMapper());
        return l;
	}

}
